first blank column number: too difficult for a beginner...

IgorDepecker

New Member
Joined
Jan 8, 2005
Messages
35
Dear,

Can someone help me with the following:

In cell A1, I want the column number of the first blank column (= going right column by column) after the active cell;

example: row B is filled until and including column D; row C is filled until and including row F; row D is filled until and including B, ...
when active cell is B1, A1 should give me 5
when active cell is C1, A1 should give me 7
when active cell is D1, A1 should give me 3
...

Any help is welcome ! thanks a lot in advance
Igor
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Re: first blank column number: too difficult for a beginner.

A couple of things...
One, this can be done with some VBA, but I don't think it can be done with a formula (I may be wrong, but...)
Two, in order to do this with VBA I'd need to get past some confusion I have over your description. (ie. "row B is filled until and including column D; row C is filled until and including row F; row D is filled until and including B, ... "
I'm having a hard time deciphering between what are your rows and what are the columns. Can you give us the row references in numbers and the column references in letters?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Re: first blank column number: too difficult for a beginner.

Even better, can you post an example? (Find the link to COlo's HTML Maker at the bottom of the page; it will let you do that).

Smitty

(Heya Dan!)
 

IgorDepecker

New Member
Joined
Jan 8, 2005
Messages
35
Re: first blank column number: too difficult for a beginner.

HalfAce said:
A couple of things...
One, this can be done with some VBA, but I don't think it can be done with a formula (I may be wrong, but...)
Two, in order to do this with VBA I'd need to get past some confusion I have over your description. (ie. "row B is filled until and including column D; row C is filled until and including row F; row D is filled until and including B, ... "
I'm having a hard time deciphering between what are your rows and what are the columns. Can you give us the row references in numbers and the column references in letters?

Thanks for your reply !
Indeed, a bit poor description... I mean row 2 has 3 entries, i.e. A2, B2 and C2; row 3 has 6 entries, i.e. A3, B3, C3, D3, E3 and F3; row 4 has 2 entries, i.e. A4 and B4, and so on;
the active cell is always somewhere in column A so if A2, the outcome (in cell A1) should be 4 (because D2 is the first blank cell in that row); if the active cell is A3, the outcome in A1 should be 7 (because G3 is the first blank cell in that row); if the active cell is A4, the outcome in A1 should be 3 (because C4 is the first blank cell in that row);

I hope you understand (but I'll download the HTML maker for future postings, makes it easier); thanks for your time !
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Re: first blank column number: too difficult for a beginner.

Hi Igor,
Don't know if you want this to be called from a button... like so:
Code:
Sub ShowMeTheNextColumn()
Dim x As Long
If Selection.Column <> 1 Or Selection.Row < 2 Then Exit Sub
If ActiveCell = "" Then Exit Sub
x = ActiveCell.Row
If Cells(x, 2) = "" Then
[A1] = 2
Else: [A1] = Cells(x, 256).End(xlToLeft)(1, 2).Column
End If
End Sub
Or if you want it to happen automatically by just selecting a cell in column A. If the latter, then this code goes into the worksheet module for the sheet of interest.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
If Selection.Column <> 1 Or Selection.Row < 2 Then Exit Sub
If ActiveCell = "" Then Exit Sub
x = ActiveCell.Row
If Cells(x, 2) = "" Then
[A1] = 2
Else: [A1] = Cells(x, 256).End(xlToLeft)(1, 2).Column
End If
End Sub
Either one should do what you've asked for.

Hope it helps,
Dan

(Hey Smitty)
 

IgorDepecker

New Member
Joined
Jan 8, 2005
Messages
35
Re: first blank column number: too difficult for a beginner.

HalfAce said:
Hi Igor,
Don't know if you want this to be called from a button... like so:
Code:
Sub ShowMeTheNextColumn()
Dim x As Long
If Selection.Column <> 1 Or Selection.Row < 2 Then Exit Sub
If ActiveCell = "" Then Exit Sub
x = ActiveCell.Row
If Cells(x, 2) = "" Then
[A1] = 2
Else: [A1] = Cells(x, 256).End(xlToLeft)(1, 2).Column
End If
End Sub
Or if you want it to happen automatically by just selecting a cell in column A. If the latter, then this code goes into the worksheet module for the sheet of interest.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
If Selection.Column <> 1 Or Selection.Row < 2 Then Exit Sub
If ActiveCell = "" Then Exit Sub
x = ActiveCell.Row
If Cells(x, 2) = "" Then
[A1] = 2
Else: [A1] = Cells(x, 256).End(xlToLeft)(1, 2).Column
End If
End Sub
Either one should do what you've asked for.

Hope it helps,
Dan

Thanks Dan, it works ! Grtz


(Hey Smitty)
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829

ADVERTISEMENT

Re: first blank column number: too difficult for a beginner.

Hello, Igor. How about a formula solution? Enter the following formula in cell A1, then coopy it down. In my formula, I have assumed that the range is column B through column Z, but, if you want to exand or contract the range, please do so.

=COLUMN(Z1)-COLUMN(B1)-COUNTBLANK(B1:Z1)+1

Do let us know how you solved your problem.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Re: first blank column number: too difficult for a beginner.

Yes, nice one Ralph.
Works great as long as there are no blanks between first & last columns of data. (and better than mine if there are and that's what you want to see the column of...)
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Re: first blank column number: too difficult for a beginner.

Half-Ace, thank you for your kind post, but, after reading a part ot Igor's second post, as follows:

"Indeed, a bit poor description... I mean row 2 has 3 entries, i.e. A2, B2 and C2; row 3 has 6 entries, i.e. A3, B3, C3, D3, E3 and F3; row 4 has 2 entries, i.e. A4 and B4, and so on;
the active cell is always somewhere in column A so if A2, the outcome (in cell A1) should be 4 (because D2 is the first blank cell in that row); if the active cell is A3, the outcome in A1 should be 7 (because G3 is the first blank cell in that row); if the active cell is A4, the outcome in A1 should be 3 (because C4 is the first blank cell in that row); "

it's clear to me that I mised his needs completely, since he wants the answer only in cell A1, and the answer shoud be according to which row is active, or selected, at that time. :oops:

And a "Heya" to Smitty!
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Re: first blank column number: too difficult for a beginner.

Half-Ace, thank you for your kind post, but, after reading a part ot Igor's second post, as follows:

"Indeed, a bit poor description... I mean row 2 has 3 entries, i.e. A2, B2 and C2; row 3 has 6 entries, i.e. A3, B3, C3, D3, E3 and F3; row 4 has 2 entries, i.e. A4 and B4, and so on;
the active cell is always somewhere in column A so if A2, the outcome (in cell A1) should be 4 (because D2 is the first blank cell in that row); if the active cell is A3, the outcome in A1 should be 7 (because G3 is the first blank cell in that row); if the active cell is A4, the outcome in A1 should be 3 (because C4 is the first blank cell in that row); "

it's clear to me that I mised his needs completely, since he wants the answer only in cell A1, and the answer shoud be according to which other cell in column A is active, or selected, at that time. :oops:

And a "Heya" to Smitty!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,352
Messages
5,635,785
Members
416,882
Latest member
ericvrealty

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
Top