I do have one more question on this topic -- what if "no cells were found", runtime error 1004? How can I code around that? I tried if x = vbNullString, but it still produces an error.
Do you have formulas in the row?
Formulas returning "" will not be considered blank by that code.
And if you have a formula like that going all the way to the last column, then that error will occur.
I certainly do not have any formulas that go to the last column. I just have a very simple MS query in column A (header row) only, and it populates down to X rows.
It's weird - this was working the first time I tested it, and now it's saying no rows found. I'm rebooting my computer and will re-test it.
edit: Update- still saying no cells found after reboot.
I just tested it on other tabs, and it works just fine. So for some reason it doesn't like the tab I need it to work on. Maybe if I delete and re-create the tab....
Maybe that query is putting some wierd non printable characters in there.
anyway, you could handle the error like this
Code:
Dim x As Long
On Error Resume Next
x = Range("1:1").SpecialCells(xlCellTypeBlanks).Column
If x = 0 Then
'put code here you want to run in the case when there are no blank cells
End If
I guess I'll really have to dig through the code and see if its "selecting" more than 8,2xx rows --I don't believe it is. I do use something like: with sheet.cells .clearcontents end with ... But I don't think that is the culprit. I also tried just selecting a cell before running the code, and that didn't work either. I may have to abandon this approach for now and come back to it later.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.