Consulting

Results 1 to 13 of 13

Thread: clear last but one line to a userform

  1. #1

    Question clear last but one line to a userform

    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
    Last edited by Aussiebear; 06-30-2022 at 03:33 AM. Reason: Added code tags to supplied code

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,783
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,577
    Without a sample file: no idea and too many possible mistakes.

  4. #4
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    810
    Location
    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
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,577
    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

  6. #6
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    810
    Location
    Removed
    Last edited by georgiboy; 06-30-2022 at 08:29 AM. Reason: Not worth it
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  7. #7

    clear last but one line to a userform

    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
    Attached Files Attached Files

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,577
    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_comb...istbox_en.html
    Attached Files Attached Files

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,783
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    810
    Location
    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
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,577
    Cells(1).currentregion.offset(1).clearcontents

  12. #12
    Thanks Georgiboy
    You have come to my rescue again.

    Regards

    Keith

  13. #13
    Thanks for your help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •