clear last but one line to a userform

[keith barnes]

Hi

I have kindly been given a macro below that clears the last line and retains the formular to a userform list box.


Sub clear()
Dim x As Long

With Sheets("database")
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(x, 1).EntireRow.SpecialCells(xlConstants).ClearContents
End With
End Sub


Is it possible to improve on the macro so that it never clears the very last row as this row contains the list box headings.

I could get around it also if the macro refered just to a range rather than the entire sheet!!!


Many thanks in advance

Regards

Keith

[SamT]

That's not a UserForm. It takes a lot of unnecessary VBA to move the last row of a sheet table to the first row of a UserForm ListBox.

Replace
.Cells(x, 1).EntireRow.SpecialCells(xlConstants).ClearContents
with
Rows(x - 1).ClearContents

[snb]

Without a sample file: no idea and too many possible mistakes.

[georgiboy]

You could set the first part of the reference within cells to be the bottom of the range you speak about, the highlighted bit below is in reference to the sheet row. I have set it to row 20 in the example below:


Sub clear()
Dim x As Long

With Sheets("database")
x = .Cells(20, 1).End(xlUp).Row
.Cells(x, 1).EntireRow.SpecialCells(xlConstants).ClearContents
End With
End Sub

It will look from row 20 up for the next blank cell

[snb]

1. Do not use reserved terms (VBA or Excel) for an own 'object', variable or macro/function: a name containing an underscore can't interfere with reserved terms.
2. Do not use unnecessary variables.


Sub M_snb()
Sheets("database").Cells(20, 1).End(xlUp).EntireRow.SpecialCells(2).ClearContents
End Sub

[georgiboy]

Removed

[keith barnes]

Hi Guys

I still cannot get the code to work that prevents the headings (row 1) of the database, to remain, when clearing the rows on the Listbox on the userform.

I have attached the workbook in the hopes that you can revisit this problem and help me out.

Regards

Keith

[snb]

You'd better use VBA and Excel's builtin options.


Private Sub UserForm_Initialize()
Entry.List = Split("Item1 Item2 Item3 Item4")
Fruit_Type.List = Split("Apple Orange Pear Banana")
Quantity1.List = Split("30000 50000 100000 Other")
Quantity1.List = Split("50 500 1000 Other")

lstDatabase.List = Sheet2.ListObjects(1).DataBodyRange.Value
End Sub

How to populate Listboxes/comboboxes: see https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[SamT]

very last row as this row contains the list box headings

prevents the headings (row 1) of the database, to remain, when clearing the rows on the Listbox
What are you trying to say?

And. . . If it's on a Worksheet, it is not a Database, it is a Worksheet data table. Or it could be a Table, which is different than a table.

Don't blame us for Micro$oft using the same name for different, but similar appearing, things. If you think Tables/tables are confusing, wait til you try to understand Charts and Charts.

[georgiboy]

Just edit the clear Macro to not clear if the row = 1:


Sub clear()
Dim x As Long

With Sheets("database")
x = .Cells(.Rows.Count, 1).End(xlUp).Row
If x > 1 Then
.Cells(x, 1).EntireRow.SpecialCells(xlConstants).ClearContents
End If
End With
End Sub

[snb]

Cells(1).currentregion.offset(1).clearcontents

[keith barnes]

Thanks Georgiboy
You have come to my rescue again.

Regards

Keith

[keith barnes]

Thanks for your help:yes