Mysmatch loop VBA for deleting empty rows in a table

[Krypt]

Hello everyone,

Hopefully one of you can relieve me of my 2 small problems.
With looking up and summarising multiple VBA codes, I have generally been able to put something together correctly and it works 90%.
One of my problems is whether it could be possible to modify the formula:
1. indicated in red : to copy only last added value mainsheet("INSCRIPTION BADGES") to Sheets(sheet1) table5 next available row.
Second is the :
2. In black marked - yellow line : to help me with the type mismatch to delete rows in a table.

Hopefully this picture is enough to explain my problems.

Many thanks for your help.

Kind regards,
Krypt

 

[dreid1011]

For the mismatch, you are comparing ListRow to Rows. Perhaps this:

Set TblRng = wb.ListObjects("Table5")
...
For Each row in TblRng.ListRows

 

[Krypt]

For the mismatch, you are comparing ListRow to Rows. Perhaps this:

Set TblRng = wb.ListObjects("Table5")
...
For Each row in TblRng.ListRows

Hello dreid1011,

thanks for reply, but it's not working.
Receive a new run-time error 438 -> object doesn't support this property or method.

For Each row in TblRng.ListRows -> on that line is now the error.

 

[dreid1011]

Did you remove the ".DataBodyRange" from the Set line two lines above as well?

 

[Krypt]

Did you remove the ".DataBodyRange" from the Set line two lines above as well?

Yes like you mentioned before and removed .DataBodyRange but still is there a error

ws.Range("F6:K" & lr).Copy _
wb.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Set TblRng = wb.ListObjects("Table5")
TblRng.RemoveDuplicates Columns:=1, Header:=xlYes

For Each row In TblRng.ListRows
If Application.WorksheetFunction.CountA(row) = 0 Then
row.Delete
End If
Next row

 

[dreid1011]

Unfortunately that was just a guess from what I was able to look up online. Without the file to poke around in, I am unsure what else to try at the moment. If you are able to share the file or a copy of it with sensitive data removed/replaced, I could take a look at it this evening. Through DropBox or another file sharing service.

 

[Krypt]

Unfortunately that was just a guess from what I was able to look up online. Without the file to poke around in, I am unsure what else to try at the moment. If you are able to share the file or a copy of it with sensitive data removed/replaced, I could take a look at it this evening. Through DropBox or another file sharing service.

Hello dreid1011,

i found another solution by changing the code copy-paste to copy pastspecial xlvalues, with this there no need of deleting empty row in the table.
It helped me also for my other issue to simplify the VBA code.

Thanks a lot to look for a solution with me.

Have a nice weekend.
Krypt

 

[dreid1011]

Great! I am glad you were able to get it resolved.