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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.