PDA

View Full Version : Solved: Remove last 4 rows from a listbox...



Macrosian
06-21-2010, 08:23 PM
Hi,

Can you please tell me that how can i remove last 4 rows from the listbox..

My listbox is connected with rowsource....

slamet Harto
06-21-2010, 09:20 PM
sample attached

Option Explicit

Private Sub UserForm_Initialize()
Dim cel As Range
Dim rng As Range
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row - 4

Set rng = Range("A1:A" & LastRow)

For Each cel In rng
Me.ListBox1.AddItem cel
Next cel

Set cel = Nothing
Set rng = Nothing

End Sub

Macrosian
06-21-2010, 10:05 PM
Run time error 70, Permission denied...

My listbox is using this command


.RowSource = "FIR!A16:F" & xlLastRow("FIR")



Function xlLastRow(Optional WorksheetName As String) As Long

'Check for optional worksheetname else use activesheet
If WorksheetName = vbNullString Then
WorksheetName = ActiveSheet.Name
End If

' find the last populated row in a worksheet
With Worksheets(WorksheetName)
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With

End Function

Macrosian
06-21-2010, 10:13 PM
I GOT IT

.RowSource = "FIR!A16:F" & lastrow <------------- from your def