remove formula blanks without loop

**Bazbuyer** · 10-15-2020, 09:46 AM

I have some VBA that copies used cells into another sheet 'register' (among other things). It ends by bringing the user to last used row in register. Unfortunately because there is a formula in the first sheet (Get new oracs2) it seems to copy a blank row every time the macro is run (which carries out other updates as well). This means that when there is new numbers to add it leaves blank rows in the register. To get round this I have a loop 'remove blanks added by formula but this unfortunately takes a long time to run. Can anyone suggest an alternative to the loop that would clear the blanks? The formula pulls the results through from a query

Please Login or Register  to view this content.

**mehmetcik** · 10-15-2020, 10:43 AM

This will delete all rows with a blank in column A. No Loops. Fast.

Please Login or Register  to view this content.

It looks complicated but isn't.

Last Row:

Please Login or Register  to view this content.

Select All Blanks

Please Login or Register  to view this content.

**Bazbuyer** · 10-16-2020, 03:27 AM

Hi Firstly thank you for replying so fast and your interest, I didnt expect a reply so soon. Unfortunately it didnt work as I would like it to. The first time I ran it (which shouldnt really be any different to subsequent - as the query has not pulled through anything different) it ran but didnt delete the blank and hence the cursor ended up in a blank row rather than the last used. On subsequent times it gives me a run time error 1004 - No cells were found, yet if I count the rows(by selecting them all) in 'get oracs2' there are 125, all with nothing in other than a formula

**mehmetcik** · 10-16-2020, 04:28 AM

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Bazbuyer** · 10-16-2020, 05:25 AM

Ok, I'll have to see if I can recreate this on my home computer (which will be in Excel 2007 and take a little more time), work protocols and firewalls will prevent me from uploading (or emailing out) even a sanitized version of the workbook. I'll mimic the query using a table - s*ds law it will probably all work outside of my actual workbook!

**Bazbuyer** · 10-19-2020, 03:37 AM

Hi

I've attached a sample I've mocked up on 2007 which is similar to the relevant sheets on my workbook. I've inserted a table to represent the query. This works with the loop and first time with your suggested formula but not thereafter. The user could run the macro even when there is nothing in the query/table (as the macro does other things - NB I've deleted these out of the sample workbook query) Many thanks