First Blank Column from L to R

[bs0d]

I'm looking for an effective way to identify the first blank column (working from left to right).

There could be columns of data with empty columns in between.

For instance:

has data: A, B, C, E, G

blank: D, F, H to End

 

[bs0d]

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.

 

[Jonmo1]

That would mean there are no blank cells in the ENTIRE row?
I find that perplexing, you have data in ALL 16,384 columns (256 in xl2003) ?

Or did you change the code to restrict the range to a certain set of columns like

x = Range("A1:Z1").SpecialCells(xlCellTypeBlanks).Column
instead of
x = Range("1:1").SpecialCells(xlCellTypeBlanks).Column

Go ahead and keep the entire row reference.
It's not a performance hit like it can be in a formula.

 

[bs0d]

I didn't change anything... it just started behaving that way.

Maybe I need to close and re-open my workbook? I just have data in column A.

 

[Jonmo1]

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.

 

[bs0d]

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.

 

[bs0d]

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....

 

[Jonmo1]

Maybe that query is putting some wierd non printable characters in there.

anyway, you could handle the error like this

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

 

[bs0d]

So when I typed some random values in a random column -- it then worked.

After deleting them, it continued to work.

Very strange.

 

[bs0d]

I found this: The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected in Excel

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.

 

[Jonmo1]

That only happens when you're using non contiguouse ranges.
We're not doing that, you did say that you didn't change the code I posted.

Can you post the whole code you're using now?