PDA

View Full Version : Solved: find the letter for the last colomn



white_flag
04-20-2010, 08:11 AM
hello

it is an possibility to find the letter of the last column used? (for example)


"Sheet1!A2:H" & xlLastRow("Sheet1")
'I like to change "H" with the last "column" used
xlLastcolumn("Sheet1") will return an number how can I transform this one in letter an then the letter to be put't in "Sheet1!A2:H" & xlLastRow("Sheet1")?

thx

p45cal
04-20-2010, 08:32 AM
instead of A1 notation, use rows/columns:
Range(sheet1!.cells(2,1),sheet1!.cells(xlLastRow("Sheet1"),xlLastColumn("Sheet1"))
You could extract the letter as you suggest with:
ColumnLetter = Split(Cells(1, xlLastColumn("Sheet1")).Address, "$")(1)
Sheet1!A2:" & ColumnLetter & xlLastRow("Sheet1").select but I don't see it as required.

Oops, I 've just realised this could be on a sheet, in which case:

OFFSET(A2,0,0,xlLastRow-ROW(A2)+1,xlLastColumn-COLUMN(A2)+1)

(I've substituted shortened versions of lastrow/column)

Or write yourself a little function to convert column numbers to column letters:
Function ColLetter(ColNumber)
ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
End Function
then use
"Sheet1!A2:" & ColLetter(xlLastColumn("Sheet1")) & xlLastRow("Sheet1")

white_flag
04-20-2010, 08:44 AM
thx Pascal

the idea is to populate an list view an I have like this:

Private Sub UserForm_Activate()

Dim cell As Range
Dim i As Long
Dim li As ListItem
On Error GoTo Eroare:

With Me.ListView1
.ColumnHeaders.Add , , "Name", Me.TextBox1.Width 'Add columns
.HideColumnHeaders = False 'set some properties
.View = lvwReport
.Gridlines = True

For Each cell In Range("B2:F2").Value
.ListItems.Add , , cell
Next cell

ListView1_ItemClick .ListItems(.SelectedItem.Index) 'fill edit controls
End With
Eroare:
'MsgBox "error"

End Sub


And I like to change Range("B2:F2") specially the last one "F2" to an variable. the problem is that I do not know how. like you put't is not going

white_flag
04-20-2010, 09:06 AM
Private Sub UserForm_Activate()
Dim LR As Long, LC As Long
With Sheets("Sheet1")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Dim cell As Range
Dim i As Long
Dim li As ListItem
On Error GoTo Eroare:

With Me.ListView1
.ColumnHeaders.Add , , "Name", Me.TextBox2.Width 'Add columns
'.ColumnHeaders.Add , , "Density", Me.TextBox1.Width
.HideColumnHeaders = False 'set some properties
.View = lvwReport
.Gridlines = True
'Range("A2:G2")
For Each cell In Range("A2", Cells(LR, LC))
.ListItems.Add , , cell
Next cell

ListView1_ItemClick .ListItems(.SelectedItem.Index) 'fill edit controls
End With

Eroare:
'MsgBox "error"
End Sub

p45cal
04-20-2010, 09:07 AM
see if you can tweak this proof of concept:
Sub blah()
Dim Cell As Range
LastCol = 4
LastRow = 5
'Range(Cells(2, 2), Cells(LastRow, LastCol)).Select 'just to check
For Each Cell In Range(Cells(2, 2), Cells(LastRow, LastCol)) '.Value 'do you need '.value'? (you've defined Cell as a range)
'.ListItems.Add , , cell
Next Cell
End Sub