hi,
can you help please with VBA to remove any extra empty rows between the tables. and keep a fixed number of row , say 1 row only. so all the vertical separation between tables will be the same.
please check the attached file.
there is actually more than two columns of tables ,but i give sample of two.
highly appreciate you help.
thanks in advance.
Use built-in Excel Tables. Then when a new table row is inserted, or a row deleted, the tables below the Table where the row was inserted or deleted will automatically move down or up accordingly. Thus maintaining a constant gap between each Table.
I am attaching your workbook with the right hand tables converted to built-in Excel Tables. Note I have also removed the bottom row of each table and replaced it with a Totals Row, that Total will adjust automatically for all changes made to a Table.
If you are still after a macro solution, I think this does what you are after - try the button on the sheet.
Sub copytables()
Application.ScreenUpdating = False
Dim ws1 As Worksheet: Set ws1 = Sheet1
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim MyList(3) As Variant ' change 3 to the number of table names you have
Dim MyTable As Variant
Dim Rnge1 As Range, c As Range
Dim x As Long, i As Long, n As Long, p As Long
Set Rnge1 = ws1.UsedRange
MyList(0) = "Table1" ' change these names
MyList(1) = "Table2"
MyList(2) = "Table3"
MyList(3) = "Table4"
i = 1
For x = LBound(MyList) To UBound(MyList)
n = 0
p = 1
For Each c In Rnge1
If c.Value = MyList(x) Then
MyTable = c.CurrentRegion
If UBound(MyTable, 1) > n Then n = UBound(MyTable, 1)
c.CurrentRegion.Copy (ws2.Cells(i + 1, p).Resize(UBound(MyTable, 1), UBound(MyTable, 2)))
p = p + UBound(MyTable, 2) + 1
End If
Next c
i = i + n + 1
Next x
Application.ScreenUpdating = True
End SubDon’t have an account yet? Register yourself now and be a part of our community!