1

I have a big excel sheet that i retrieve information from into a new sheet that has a table. The problem is i cant know the number of rows the table will take because the data is dynamic. I also have to print the table afterwards but it copies the blank rows from the raw data. and so what i have done is, i created this code below to filter the table and hide all the empty rows. and it does exactly what i want-hiding the empty rows. But everytime the data in the table needs to be refreshed(copy and pasted) excel asks me with a dialogue box asking if i want to delete entire sheet row. if i click yes, it does refresh the data but if i click no it continues to add the new data under what was originally in the table.--this is not what i want.

 LastRow = Worksheets("KYC Feedback Page 2").Range("A65536").End(xlUp).Row
LastColumn = Replace(Cells(1, 
ActiveSheet.Range("XFD1").End(xlToLeft).Column).Address(False, False), "1", 
"")
On Error Resume Next
With Worksheets("KYC Feedback Page 2").Range("A1:" & LastColumn & LastRow)
.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
End With
On Error Resume Next
Sheet1.AutoFilterMode = False
Community
  • 1
  • 1
cookiemonster
  • 368
  • 1
  • 8
  • 22

1 Answers1

2

I'm not sure if i understood you correctly, but try this piece of code. You can achieve your request I think by adding Application.DisplayAlerts to your code:

Application.DisplayAlerts = False

LastRow = Worksheets("KYC Feedback Page 2").Range("A65536").End(xlUp).Row
LastColumn = Replace(Cells(1, 
ActiveSheet.Range("XFD1").End(xlToLeft).Column).Address(False, False), "1", 
"")
On Error Resume Next
With Worksheets("KYC Feedback Page 2").Range("A1:" & LastColumn & LastRow)
.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
End With
On Error Resume Next
Sheet1.AutoFilterMode = False

Application.DisplayAlerts = True
maaajo
  • 839
  • 6
  • 10