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

[IgorDepecker]

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

 

[HalfAce]

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]

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]

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?

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]

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:

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.

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]

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:

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.

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]

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]

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]

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.

And a "Heya" to Smitty!

 

[RalphA]

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.

And a "Heya" to Smitty!