When I copy and paste a sheet of hidden formulas (for instance “=IF('data'!A2="","",data'!A2)”) to another sheet, it pastes everything meaning that it copies empty and not empty rows. This issue is not a big deal if I only use excel but if I use a reporting tool like Power BI as data, the tool will not display correctly the result. This macro will count the number of rows of the main source then delete the empty rows.

 

When I use the macro ?

When I need to delete the empty cells that were copied from a sheet of hidden formulas

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
' change data by the name of your main source sheet
n = Worksheets("data").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
' change sheet1 by the name of the sheet where to delete the empty rows
Worksheets("sheet1").Rows(n + 1 & ":" & Worksheets("sheet1").Rows.Count).delete
End Sub