First Blank Column from L to R

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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
 
It was having issues, I might have been able to figure it out but in the interest of time, I had to move on. I might have tested using a select statement to make sure the desired tab was at focus.

But my workaround was to just keep incrementing the max column. For my project and number of entities, I would not be in a position to run out of columns - even though filling the blanks (essentially a left align) would have been more visually favorable.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
It was having issues, I might have been able to figure it out but in the interest of time, I had to move on. I might have tested using a select statement to make sure the desired tab was at focus.

But my workaround was to just keep incrementing the max column. For my project and number of entities, I would not be in a position to run out of columns - even though filling the blanks (essentially a left align) would have been more visually favorable.

I missed the original thread when it first appeared. The problem with SpecialCells is, with a couple of exceptions, it only "sees" the UsedRange for the sheet it is operating on.... if you have all the columns filled with at least one value out to the end of your data, the blank column to the right of the last used column falls outside of the UsedRange, so as far as SpecialCells is concerned, there is no blank to find. Now, as for the question of the column number of the first blank column, This should work...

ColumnNumberOfFirstBlankColumn = Range("A1").CurrentRegion.Columns.Count + 1
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

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.
Go back
Back
Top