VBA to combine column letter and row number?

[EssKayKay]

I’m not sure this is possible but what I am looking for VBA code to combine a Column letter and Row number where the row changes. I have a cell with range name (LastPmt) that holds the row number.

As example: The last payment is in cell M392. I want a range to be “M33:M392”. However, the row number changes (occasionally the column changes but for now let’s only address the row). Therefore the range should read something like:

Range("M33:M & LastPmt").Select

Thanks for viewing,
Steve

 

[Joe4]

Remember the following:

Literal text goes between double-quotes.
Variables must be outside double-quotes.
Join the two together with "&", i.e.

Range("M33:M" & LastPmt).Select

 

[EssKayKay]

Remember the following:

Literal text goes between double-quotes.
Variables must be outside double-quotes.
Join the two together with "&", i.e.

Range("M33:M" & LastPmt).Select

Hello Joe,
Thank you for your quick response. I modified my code as recommend. Initially I received a Compile error – variable not defined so I added:

Dim LastPmt As Character

However then I received various Run-time errors (91, 1004). I was not sure what variable type to use. Besides Character, I tried String, Label, and Integer.
Any other suggestions would be appreciated.
Again, thanks for viewing. . .

 

[Joe4]

Since you storing the row number, use a numeric type, i.e.

Dim LastPmt As Long

("Long" stands for "Long Integer")

But that is only if "LastPmt" is a variable you are setting in VBA.
If it is the name of named range from your Excel sheet, you reference it differently.

 

[Joe4]

If the value is stored in a range name "LastPmt" on your sheet, then you would access it like this:

Range("M33:M" & Range("LastPmt").Value).Select

 

[EssKayKay]

WOW, that was fast – thank you.
I changed to: Dim LastPmt As Long
However I’m still getting a
Run-time error ‘1004’ – Method ‘Range’ of object ’_Worksheet’ failed.

 

[Joe4]

Please look at my previous 2 posts.

 

[EssKayKay]

Please look at my previous 2 posts.

Ahhh - thank you one more time Joe. This worked perfectly. Sorry about being so impatient. I posted my response before the others were visible.
Now on to my next concern. . .

Again, much appreciated,
Steve

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[EssKayKay]

You are welcome.
Glad I was able to help!

If the value is stored in a range name "LastPmt" on your sheet, then you would access it like this:

Range("M33:M" & Range("LastPmt").Value).Select

Joe,

I just wanted to thank you one more time. I've used your suggestion here numerous times with a few variations. It works great!