Find the Address of the Last Row or Column

[HunterN]

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

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H
1	1	a	b	c	c	c	c
2	2
3	3
4	4				c
5	5
6	6
7	7
8
Sheet1

 

[HunterN]

Hi,

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

Thanks,

 

[HunterN]

Any suggestions?

Thanks, Nancy

 

[Peter_SSs]

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]

I use this method:

http://www.beyondtechnology.com/geeks012.shtml

My 100th post!!!! Can I have a medal now please?

 

[Peter_SSs]

I use this method:

http://www.beyondtechnology.com/geeks012.shtml

My 100th post!!!! Can I have a medal now please?

I am not sure that there are any spare medals lying around, but I'll shout you an imaginary beer.

 

[Darren Bartrup]

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.

 

[HunterN]

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.


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