Solved: find the letter for the last colomn

[white_flag]

hello

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

[vba]
"Sheet1!A2:H" & xlLastRow("Sheet1")
'I like to change "H" with the last "column" used
[/vba] 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]

instead of A1 notation, use rows/columns:
[vba]Range(sheet1!.cells(2,1),sheet1!.cells(xlLastRow("Sheet1"),xlLastColumn("Sh eet1"))[/vba]
You could extract the letter as you suggest with:
[vba]ColumnLetter = Split(Cells(1, xlLastColumn("Sheet1")).Address, "$")(1)
Sheet1!A2:" & ColumnLetter & xlLastRow("Sheet1").select[/vba] 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:
[VBA]Function ColLetter(ColNumber)
ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
End Function[/VBA]
then use
"Sheet1!A2:" & ColLetter(xlLastColumn("Sheet1")) & xlLastRow("Sheet1")

[white_flag]

thx Pascal

the idea is to populate an list view an I have like this:
[VBA]
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
[/VBA]

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]

[VBA]
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

[/VBA]

[p45cal]

see if you can tweak this proof of concept:
[VBA]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
[/VBA]