Find the Address of the Last Row or Column

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
Hi

What is the quickest way using VBA, to find the last column used on a
sheet so I know the column number and also the address of this cell.


Thanks,
Nancy
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
Hi

What is the quickest way using VBA, to find the last column used on a
sheet so I know the column number and also the address of this cell.


Thanks,
Nancy
Nancy, for the sample below, the last column is column G, but what cell address would you expect for the second part of your question for this sample data?
Mr Excel.xls
ABCDEFGH
11abcccc
22
33
44c
55
66
77
8
Sheet1
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
Hi,

Is this case, my data is contiguous. I do have blank rows but no
blank columns.

Thanks,
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nancy

Something like this?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LastColumnAndCell()

    <SPAN style="color:#00007F">Dim</SPAN> LastCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastColumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

    LastCell = Cells.SpecialCells(xlLastCell).Address(ColumnAbsolute:=False)
    LastColumn = Left(LastCell, InStr(LastCell, "$") - 1)
    LastCell = Replace(LastCell, "$", "")

    MsgBox "Last column is " & LastColumn & Chr(10) & "Last cell is " & LastCell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Great, thanks.

That means I have to go home now for being at work under the imaginary influence (and suffer an imaginary hang-over and the imaginary disciplinary when I get back).

Sorry, fell off topic there. :oops:
 

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
Good Morning EST

Thank you very much (both of you) - Peter and Delmar

Peter gave me just what I needed in order to strip down the Lastcols
letter. And the other example is perfect too. Actually I had found
that website a couple of years ago and had seen that function before.

Cheers to both of you.
(y)

Now since it's only 8:30 AM you both can go home and have hour beer.


And I can stay here and continue working on this project that seems to be
taking me forever to finish. :wink:

Nancy
 

Forum statistics

Threads
1,141,721
Messages
5,708,092
Members
421,546
Latest member
delatollas

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