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,814
    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,635
    Without a sample file: no idea and too many possible mistakes.

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Removed
    Last edited by georgiboy; 06-30-2022 at 08:29 AM. Reason: Not worth it
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  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,635
    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,814
    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
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

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