Select first row of filtered data and delete

    I have a complex userform where I am using it to overwrite and save data in a filtered data set. I am using a filter in part of my user form upon a button click to filter data based on text box value in the user form. After changing information in the user form, I would like to select the data being filtered (first row of data beneath filter) and delete it so that I can copy and paste the data in the form to the dataset and have unique values and not duplicate.


    The Filter Row headers are on Row 4. The row I want to select will be right below the header (always only 1 selection), but could be any row value based upon when it was entered into sheet. Essentially I need to be able to select the first filtered row beneath the header and delete that row.


    The code below I have found, but it is deleting more rows than the one being filtered.


    Code
    Private Sub YesCancel1_Click()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Database").Range("A4:AN" & LR).SpecialCells(xlCellTypeVisible).Select
Selection.Delete
End Sub



    This is the last bit of code to complete my tool. Your help is appreciated!

    Edited once, last by royUK: Add Code Tags, please read the Forum Rules to understand why. ().

    Hello and Welcome to the Forum :)


    You could test the following


    Code
    Private Sub YesCancel1_Click()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Database").Range("A4:AN" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Offset(1).Delete
End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Try this

    Code
    Private Sub YesCancel1_Click()
Sheets("Database").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1).EntireRow.Delete
End Sub

    royUK


    That code works, but it isn't deleting just the row below the filter headers as I have 5 total rows of data to start, then I end with 4. Then i perform the macro again and down to 3 and so on. Eventually just left with the last entry that I modified.


    Row 4 has the headers and I am filtering based on column A. When filtered, I need to have only the row filtered deleted, none others.


    Any suggestions?

    Have you tested the macro posted in message # 2 ...


    It does assume you have already applied your Filter ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    I've just checked the code that I posted and it is only deleting the first filtered row. Please attach a small example workbook of your code.


    Also, why are you deleting rows by this method - running the code multiple times?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login