VBA range selection question

[Pete]

Need to use VBA select all the blank cells, one column to the right of
a column which has a variable range.
example:
Have values in a column, B3:B10. Need to select C3:C10.

The column B range size changes all the time, but always starts in
cell B3, and can range from 50-500 cells.. I'll be entering formulas
in column C, and don't want to enter any formulas where there isn't a
value in column B, so, it necessitates chosing only the blanks that
has a value preceeding it, in column B.

Have located lots of VBA relative navigation codes, but none that will
select this column.
TIA for any ideas.
Pete

 

[Gord Dibben]

No selection necessary, just fill in the blanks.

Adjust "My Formula" to suit.

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 2).Value <> "" Then
Cells(X, 2).Offset(0, 1).Formula = "=My Formula"
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[Pete]

Thank you Gord for your reply and answer. 2 things that I might
explore further: . .

The formula I'm entering would be a simple subtraction from the 2
cells to the left.
If I entered in the "=your formula" section the following:
"=B3-A3"
the macro repeats that exact formula all the way down the cells that
have been filled in. . .they all refer to B3-A3.
How would I adjust this to apply to the row that follow and now has a
formula in it? (B4-A4, B5-A5, etc.)

The second thing is assuming new column references. . .
Blank could be N3, and populated columns would be L and M. N is where
to place the formula.
I'm not sure which of the numbers in your code example would refer to
column 14 (the N column)

Many thanks.
Pete

 

[Gord Dibben]

Revised to account for both your questions.

13 is column M which contains data and blanks. We want to use column M as base
for LastRow

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 13).Value <> "" Then
Cells(X, 13).Offset(0, 1).Formula = "=M" & X & "-L" & X
End If
Next X
Application.ScreenUpdating = True
End Sub

If you want a relative rather than hard-coded macro try this
one..............just remember that it all starts with you selecting a cell in
whichever column you want to be LastRow column............B or M or whatever.

Note also the addition of the GetColLet UDF

Sub Insert_Formula()
Dim LastRow As Long
Dim X As Long
Dim Y As String
Dim Z As String
Y = GetColLet(ActiveCell.Column)
Z = GetColLet(ActiveCell.Column - 1)
LastRow = Cells(Rows.Count, Y).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, Y).Value <> "" Then
Cells(X, Y).Offset(0, 1).Formula = _
"=" & Y & X & "-" & Z & X
End If
Next X
Application.ScreenUpdating = True
End Sub

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function


Gord

 

[Pete]

You, Sir; are BRILLIANT! Need I say more?

Thank you so much.
Pete

 

[Gord Dibben]

Thanks is great..............the brilliant part might be over the top<g>

Glad to help.


Gord

 

[Rick Rothstein]

Note also the addition of the GetColLet UDF

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

Here is a more concise version of your GetColLet function which the OP might
want to consider using...

Function GetColLet(ColNumber As Long) As String
GetColLet = Split(Cells(1, ColNumber).Address, "$")(1)
End Function

Rick Rothstein (MVP - Excel)

 

[Gord Dibben]

Thanks Rick

Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function


Gord

 

[joeu2004]

Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
    GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

One way:

GetColNum = Range(myColumn & 1).Column

 

[joeu2004]

One way:
GetColNum = Range(myColumn & 1).Column

Another way:

GetColNum = Cells(1, myColumn).Column

 

[Gord Dibben]

This is one letter shorter than your first suggestion.

Thanks


Gord

 

[Gord Dibben]

This is one letter shorter than your first suggestion.

Thanks


Gord

 

[Rick Rothstein]

Can you "more concise" this one?

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

The Columns property does not require the repeated, colon delimited string
like the Range does...

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn).Column
End Function

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

Another way:

GetColNum = Cells(1, myColumn).Column

The row number is optional, so you can save a character...

GetColNum = Cells(, myColumn).Column

This makes the statement the same length as the one I just posted a few
minutes ago, namely...

GetColNum = Columns(myColumn).Column

Rick Rothstein (MVP - Excel)

 

[joeu2004]

This is one letter shorter than your first suggestion.

I didn't think of it that way. I don't really count characters,
although I know you were interested in being "more concise".

I tend to measure "conciseness" in terms of number and complexity of
apparent operations, not number of characters.

I thought it would be more significant that Cells(1,mycol) does not
require the concatenation that Range(mycol & 1) does.

But I am surprised to learn that both perform about the same.

Actually, the Range implementation is about 5% faster on my
computer(!). But we're only talking about 0.3 microsec on my computer
(YMMV). And I haven't determined if that is statistically
significant.

Not surprisingly, the implementation below is about 55% faster than
the Cells and Range implementations. But it does not qualify as "more
concise", of course.

I'm surprised that it is "only" 54-66% faster, since it does not
require the interprocess communication that I presume the Cells and
Range implementations do.

I somewhat confirmed that they do by substituting Range("a1").Value,
which presumably must communicate with the Excel process. (Of course,
it does not perform the same functionality. That was not its
purpose.)

Note: Initially, everything appeared to perform about the same. Then
I remembered that VBA adds overhead the first time each code path is
executed for __each__ call(!). So for the final comparison, I looped
20 times within the call and averaged the time ignoring the first
iteration. (I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.) Also, I sync'd with the process clock and booted the
system in safe mode without networking in order to minimize extraneous
system activity.

-----

A non-concise, but faster implementation....

Function getcolnum(mycolumn As String) As Long
Const lcA As Long = 97 'Asc("a")
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA + 1
Else
getcolnum = _
(Asc(LCase(Left(mycolumn, 1))) - lcA + 1) * 26 _
+ Asc(LCase(Right(mycolumn, 1))) - lcA + 1
End If
End Function

The second getcolnum expression can be simplified by rearranging
terms, to wit:

Const lcA27 As Long = 27 * lcA
[....]
getcolnum = _
26 * Asc(LCase(Left(mycolumn, 1))) _
+ Asc(LCase(Right(mycolumn, 1))) - lcA27 + 27

 

[Gord Dibben]

Thanks Rick


Gord

The Columns property does not require the repeated, colon delimited string
like the Range does...

Function GetColNum(myColumn As String) As Long
GetColNum = Columns(myColumn).Column
End Function

Rick Rothstein (MVP - Excel)

 

[Gord Dibben]

All I can say is Wow!

Thanks for researching this subject so thoroughly.


Gord

This is one letter shorter than your first suggestion.

I didn't think of it that way. I don't really count characters,
although I know you were interested in being "more concise".

I tend to measure "conciseness" in terms of number and complexity of
apparent operations, not number of characters.

I thought it would be more significant that Cells(1,mycol) does not
require the concatenation that Range(mycol & 1) does.

But I am surprised to learn that both perform about the same.

Actually, the Range implementation is about 5% faster on my
computer(!). But we're only talking about 0.3 microsec on my computer
(YMMV). And I haven't determined if that is statistically
significant.

Not surprisingly, the implementation below is about 55% faster than
the Cells and Range implementations. But it does not qualify as "more
concise", of course.

I'm surprised that it is "only" 54-66% faster, since it does not
require the interprocess communication that I presume the Cells and
Range implementations do.

I somewhat confirmed that they do by substituting Range("a1").Value,
which presumably must communicate with the Excel process. (Of course,
it does not perform the same functionality. That was not its
purpose.)

Note: Initially, everything appeared to perform about the same. Then
I remembered that VBA adds overhead the first time each code path is
executed for __each__ call(!). So for the final comparison, I looped
20 times within the call and averaged the time ignoring the first
iteration. (I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.) Also, I sync'd with the process clock and booted the
system in safe mode without networking in order to minimize extraneous
system activity.

-----

A non-concise, but faster implementation....

Function getcolnum(mycolumn As String) As Long
Const lcA As Long = 97 'Asc("a")
If Len(mycolumn) = 1 Then
getcolnum = Asc(LCase(mycolumn)) - lcA + 1
Else
getcolnum = _
(Asc(LCase(Left(mycolumn, 1))) - lcA + 1) * 26 _
+ Asc(LCase(Right(mycolumn, 1))) - lcA + 1
End If
End Function

The second getcolnum expression can be simplified by rearranging
terms, to wit:

Const lcA27 As Long = 27 * lcA
[....]
getcolnum = _
26 * Asc(LCase(Left(mycolumn, 1))) _
+ Asc(LCase(Right(mycolumn, 1))) - lcA27 + 27

 

[joeu2004]

Errata....

So for the final comparison, I looped 20 times within the
call and averaged the time ignoring the first iteration.
 (I did not display the first-iteration time to confirm
that it is 2-5 times longer than the average of the other
iterations.)

Arrgghh! I meant: I __did__ display the first-iteration time.

Usually, I err by omitting "not" where it is important. (Doh!)
Adding "not" needlessly is a new wrinkle.

BTW, I wrote "55% faster" in one place and "54-66% faster" in
another. Although it's not important, the former is correct. I had
changed the implementation, which made the execution time consistent.
But when I edited the posting-in-progress, I fixed one comment, but
not the other.

'Nuf said. The horse is dead.

 

[Rick Rothstein]

Just out of curiosity, how does this function compare speed-wise to the
function you posted?

Function GetColNum(myColumn As String) As String
Dim X As Long, B() As Byte
B = UCase(myColumn)
GetColNum = B(UBound(B) - 1) - 64
If UBound(B) > 1 Then GetColNum = GetColNum + 26 * (B(0) - 64)
End Function

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

Dim X As Long, B() As Byte

Uh, you can omit the declaration for the X variable (it was left over from a
different attempt).

Rick Rothstein (MVP - Excel)