Return LETTER of ActiveCell Column 1

[jdttek]

I am using the following to select the last cell of Column A:

Range("A"& Rows.Count).End(xlUp).Select

This of course works, but only for Column A.

I need to dynamically change "A" to the Column LETTER of the ActiveCell so that the last cell in the ActiveCell column is selected when I run this code (rather than hardcoding the column).

I have tried:


Range(ActiveCell.Column & Rows.Count).End(xlUp).Select

but it returns the NUMBER of the Column so that does not work.

Can you pls suggest code that wld simply return the ActiveCell column letter to replace "A" in the Range arguement.

Thanks

JDTTEK

 

[rmikesmith]

JDTTEK,

You were on the right track. BTW...You don't need to use the letter designations for columns, and in fact, you'll find it easier to use numeric indexes in code. The following statement will do what you are asking:

ActiveSheet.Cells(65536, ActiveCell.Column).End(xlUp).Select

Note: this works in all cases except when the cell having row index 65536 (the last row in Excel worksheets) is not empty.

HTH
M. Smith

 

[pvw]

Hello JDTTEK

Using "Cells()" is the best way to select the last used cell. If you're in a situation where you really need the column letter (f.e. to show it to the user), you can use the following function:

Function GetColumnName(intColumn As Integer) As String
If intColumn <= 0 Or intColumn > Columns.Count Then
GetColumnName = &quot;#VALUE!&quot;
Else
GetColumnName = Left$(Cells(1, intColumn).Address(False, False), Len(Cells(1, intColumn).Address(False, False)) - 1)
End If
End Function

The function returns the letter. Call the function in VBA with &quot;CL = GetColumnName(CN)&quot;, where CN is the Column Number, CL contains the returned Column Letter. You can use the function in a cell with a reference: &quot;=GetColumnName(A1)&quot;, where in cell A1 is the column number. A value<1 or >256 returns an error.

HTH
Philipp

 

[ratman]

Consider either of these:

lastcontiguousrow = Range(ActiveCell.Address).End_(xlDown).Row
lastusedrow = ActiveCell.SpecialCells(xlLastCell).Row

 

[DaleWatson123321]

JDTTEK,

All of the above suggestions are quite reasonable options.

However, because you used the term &quot;dynamic&quot;, I thought I would offer the following routine, which you might agree is &quot;dynamic&quot;.

PREFACE
======
This routine is different in that it employs the use of &quot;Range Names&quot;. You might already be aware of the value of using Range Names, but for the benefit of &quot;anyone&quot; reading this, here's the primary reason to use range names.

Internally, whenever a column or row is inserted &quot;within&quot; the existing boundaries of a range name, Excel automatically adjusts the coordinates of the range name. Therefore, VBA code that uses these range names will NOT require modification each time columns or rows are inserted, or when data is moved from one location to another. In manipulating blocks of data, whether it be in sorting, extracting or moving the data, employing range names in VBA is EXTREMELY useful.

Because a block of data can &quot;grow&quot; not just by the number of rows, but also by having to add more COLUMNS, the following routine makes provision for easily adjusting for these extra columns - for those cases where the extra columns need to be added &quot;outside&quot; (to the right of) the existing named range. As previously mentioned, because these extra columns are &quot;outside&quot; the existing range name, the range name would NOT automatically be adjusted. In the following routine, it employs the use of another range name (&quot;endcolm&quot which is assigned to a cell in the last column of the data range. After adding the extra column(s), it's just a matter of re-assigning the one range name (&quot;endcolm&quot to any cell in the last column. The range name being used in this routine to define the data range is simply &quot;data&quot;.

ROUTINE
=======
Sub Reset_DataRange()
Application.ScreenUpdating = False
Application.Goto Reference:=&quot;data&quot;
firstcell = ActiveCell.Address
cur_colm = ActiveCell.Column
end_colm = Range(&quot;endcolm&quot.Column - cur_colm
lastcell = Cells(65536, cur_colm).End(xlUp).Offset(0, end_colm).Address
setrange = firstcell & &quot;:&quot; & lastcell
Range(setrange).Name = &quot;data&quot;
Application.Goto Reference:=&quot;R1C1&quot;
Application.ScreenUpdating = True
End Sub

As you'll see when you use this routine, it won't matter where your &quot;data&quot; range is located - i.e. it can start and end in &quot;any&quot; column.

The end &quot;row&quot; will be determined by the last used cell in the first column of your &quot;data&quot; range.

&quot;End_(xlDown)&quot; is an &quot;option&quot; to determine the last used row. However, it requires that there be no BLANK cell(s) in the first column of your data.

I hope this helps you in meeting your &quot;dynamic&quot; objective. Please advise as to how you make out.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[dwilson01]

Here is my 2 cents, I found this recently as I was using some of these other methods to do the same thing you are asking.

Dim lLastRow As Long, iLastCol As Integer

With ActiveSheet.UsedRange
lLastRow = .Row + .Rows.Count - 1
iLastCol = .Column + .Columns.Count - 1
End With
Debug.Print lLastRow & &quot; &quot; & iLastCol


This returns the last row and column that contains data.


Regards,

Dave

 

[jdttek]

Thanks to all for your very helpful suggestions. It has taken a while to set eachone up and test (which I am still doing). For my specific purpose, rmikesmith's sugestion seems to work the best. I have delimted a greenbar report and need to copy the subtotal account name up to each of the individual invoice records that do not contain the acct name and tht as the most stright forward way. I certainly learned alot from the other suggestions and will probably find a way to use each ofthem somewhere. Amazing how many ways there are to look at the same problem.

By the way, I posted a BIG PROBLEM on 6/27 in the Access Other Topics formum having to do with a Pwr Pt link locking my Access mdb file by creating its own DB password. If anyone is familiar with this problem, I wld appreciate if you could help out since I have gotten no responses and am getting desparate. I can access the DB, but only thru the Pwr Pt link so I obviously can't hand this project over to my client with that awkward requirement!! Otherwise, since blocked, I have 6 months of development down the tubes that I must recreate or reconcile with my latest backup. Any suggestions appreciated.

JDTTEK

 

[jdttek]

Thanks to all for your very helpful suggestions. It has taken a while to set eachone up and test (which I am still doing). For my specific purpose, rmikesmith's sugestion seems to work the best. I have delimted a greenbar report and need to copy the subtotal account name up to each of the individual invoice records that do not contain the acct name and tht as the most stright forward way. I certainly learned alot from the other suggestions and will probably find a way to use each ofthem somewhere. Amazing how many ways there are to look at the same problem.

By the way, I posted a BIG PROBLEM on 6/27 in the Access Other Topics formum having to do with a Pwr Pt link locking my Access mdb file by creating its own DB password. If anyone is familiar with this problem, I wld appreciate if any of you could help out since I have gotten no responses and am getting desparate. I can access the DB, but only thru the Pwr Pt link so I obviously can't hand this project over to my client with that awkward requirement!! Otherwise, since blocked, I have 6 months of development down the tubes that I must recreate or reconcile with my latest backup. Any suggestions appreciated.

JDTTEK

 

[dwilson01]

One last post here for me, I thought it was kinda interesting, have a look. This rutine finds the max value plus ties for the max and prints there address to a debug window.

Sub MaxVal()

Dim fa As String

iMax = WorksheetFunction.Max(ActiveSheet.UsedRange)

With ActiveSheet.UsedRange
Set rng = .Find(iMax)
fa = rng.Address
If Not rng Is Nothing Then Debug.Print rng.Address
Do
Set rng = .FindNext(rng)
If rng.Address <> fa Then Debug.Print rng.Address
Loop While Not rng Is Nothing And rng.Address <> fa
End With

End Sub


Okay, I'm done