VBA messes up table range when deleting final row

[scoobster]

Hello.

On 'sheet1' I have a table range named 'PickList' and under the header users are able to add rows. As each row is added a formula is copied to the new row because it's a table range. This is working great.

Via a button on the sheet, a user can delete any rows that has the value 'No' in the 2nd column titled 'Export'. I use 'autofilter' to select the records which meet the criteria and then the code deletes the returned rows. This is working fine.

Where I hit a problem is when it comes to the point where no rows will be left in the table range following the delete. What I get is an empty row under the header row. What I actually want is just the header row to appear so that the user can start adding rows again and these then appear within the table range.

Code is shown below;

Private Sub cmdRemoveItem_Click()
Const FilterString As String = "No"

Dim xval As Variant

Application.ScreenUpdating = False

With worksheets("sheet1").Range("PickList")

.AutoFilter field:=2, Criteria1:=FilterString

Set rngToCheck = worksheets("sheet1").Range("PickList")

For Each xval In .SpecialCells(xlCellTypeVisible)
.EntireRow.Delete
Next xval

.AutoFilter

End With

Application.ScreenUpdating = True

End Sub

Is there a better way to do what I want, or any suggestions on how to change my code so that I get the expected results?

Also, how would I check the number of records returned by the 'autofilter' as I only want the code to run if at least 1 row is returned?

Many thanks for your support.

Paul

 

[scoobster]

Hi folks,

Sorry to bump the thread. My head is about to explode as everything I found on this form points to the code I have used but it doesn't work 100% as I expect.

Thanks again for any advice.

Paul

 

[scoobster]

Think I sorted it. As I'm using a range then there must be at least one row after the header. I thought that it should be just the header showing when no data rows were present.