First Blank Column from L to R

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Apr 8, 2014

• #2

Are you talking about the ENTIRE column being Blank?
Or just in 1 specific row?

 

Upvote 0

bs0d

Well-known Member

Joined

Dec 29, 2006

Messages

622

• Apr 8, 2014

• #3

Entire column

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Apr 8, 2014

• #4

You could try

Code:

Sub test()
For i = 1 To Columns.Count
    If Cells(1, i).End(xlDown).Row = Rows.Count Then
        MsgBox "The first empty column is: " & Columns(i).Address
        Exit For
    End If
Next i
End Sub

 

Upvote 0

bs0d

Well-known Member

Joined

Dec 29, 2006

Messages

622

• Apr 8, 2014

• #5

Would it be easier or more efficient code if blank cells in the first row represented the blank column (those that have data, have a value in row 1)?

Maybe

Code:

blankCol = Cells(1, Columns.Count).End(xlToLeft).Column
If blankCol >= 1 Then
blankCol = blankCol + 1
End If

The problem with this is- it detects the first blank column from the right (on the end) and does not identify the first blank between columns that have data (which would be from the left).

 

Last edited: Apr 8, 2014

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Apr 8, 2014

• #6

Definately easier if a specific row can be used..

Code:

Sub test2()
x = Range("1:1").SpecialCells(xlCellTypeBlanks).Column
MsgBox "The first empty column is: " & Columns(x).Address
End Sub

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Apr 8, 2014

• #7

I've been assuming you're working in VBA..

If you're after a formula, this will give you the column #
Entered with CTRL + SHIFT + ENTER
=MATCH(1,1/(1:1=""),0)

 

Upvote 0

bs0d

Well-known Member

Joined

Dec 29, 2006

Messages

622

• Apr 8, 2014

• #8

Yes, working in VBA. I had the code from post #5 to find the first blank row on the end, but wanted to see how to find first blank between data. Your VBA code above seems to be working well for me.

I have a query in column "A". Whenever I key in data in column b, c, d, e -- it's applying sort of a filter style to the column and it won't let me clear the contents. So this is screwing things up for me.

How can I keep Excel from applying the auto-filter or whatever it is from the query in column A -- over to the other columns that get data?

 

Last edited: Apr 8, 2014

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Apr 8, 2014

• #9

Glad that's working for you.
Not sure I understand the new issue.

Probably best to make a new thread on it.

 

Upvote 0

bs0d

Well-known Member

Joined

Dec 29, 2006

Messages

622

• Apr 8, 2014

• #10

Thanks, and I started a new thread. I experienced this after keying-in test data in row1 at the different columns.

http://www.mrexcel.com/forum/excel-...ery-auto-formatting-override.html#post3772874

 

Upvote 0