0

I have worksheet (protected) with 5 tables, i want to copy the data from all the tables without blank row's to another worksheet. The code for copying works but the code for removing blank rows doesn't. On the first 'Selection.EntireRow.Delete' it generates the Runtime 1004 Error.

Sheets("Invulblad").Select
ActiveSheet.Unprotect "Password"

Dim ws As Worksheet
Dim tbl1 As ListObject
Dim tbl2 As ListObject
Dim tbl3 As ListObject
Dim tbl4 As ListObject
Dim tbl5 As ListObject

Set ws = Sheets("Invulblad").Select
Set tbl1 = ws.ListObjects("Tabel1")
Set tbl2 = ws.ListObjects("Tabel2")
Set tbl3 = ws.ListObjects("Tabel3")
Set tbl4 = ws.ListObjects("Tabel4")
Set tbl5 = ws.ListObjects("Tabel5")

tbl1.ListRows.Add
tbl2.ListRows.Add
tbl3.ListRows.Add
tbl4.ListRows.Add
tbl5.ListRows.Add

Sheets("Invulblad").Select
Range("Tabel1[[#All],[Afdelingspecifiek]:[Aantal personen]]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range("Tabel2[[#All],[Individueel]:[Aantal personen]]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range("Tabel3[#All]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range("Tabel4[#All]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
ActiveWindow.SmallScroll Down:=15

Range("Tabel5[#All]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

ActiveSheet.Protect "Password"
Wilco van der Helm
  • 1
  • 1
  • 1
    You will profit a lot from reading [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Those `.Select`s are not necessary and are most likely the cause of your error. – riskypenguin Oct 11 '19 at 08:00
  • 2
    As @M.Schalk says, you ought to re-write this to remove the `.Select` - but looking at what you have written there is little to go wrong. One thing that might cause an issue though is if one of your tables doesn't have any `SpecialCells(xlCellTypeBlanks)` then this would probably cause an error. – CLR Oct 11 '19 at 08:32
  • @CLR Yes, that's why i insert a blank row first in all the tables 'tbl.ListRows.Add'. I tried the following code but still got the same error : Dim rng1 As Range Dim rng1cell As Range Set rng1 = Range("Tabel1[[#All],[Afdelingspecifiek]]") Set rng1cell = rng1.SpecialCells(xlCellTypeBlanks) rng1cell.EntireRow.Delete – Wilco van der Helm Oct 11 '19 at 09:01
  • I'm presuming that **`[#All]`** is a column in your table and that's not you trying to refer to all columns in the table? Also, can you provide a sample of your table in your question? – Zac Oct 11 '19 at 10:14
  • Found it : With ListObjects("Tabel1") .ListColumns("Aantal personen").DataBodyRange.SpecialCells(xlCellTypeBlanks).Rows.Delete End With – Wilco van der Helm Oct 11 '19 at 10:28

0 Answers0