Excel - Macro to detect and hide blank rows

April 2018

I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will unhide the rows and copy values into the next available blank row and hide the remaining blank one. Unhide the 20 rows below it, delete one of them and re-hide them.
You can try this:
Private Sub Macro1()

Dim j
Dim x
j = 2
x = 3

Cells(j, 2) = ActiveCell.Offset(0, 1).Value
Cells(j, 3) = ActiveCell.Offset(0, 2).Value
Cells(j, 4) = ActiveCell.Offset(0, 3).Value
Cells(j, 5) = ActiveCell.Offset(0, 4).Value
Cells(j, 6) = ActiveCell.Offset(0, 5).Value
Cells(j, 7) = ActiveCell.Offset(0, 6).Value
Cells(j, 8) = ActiveCell.Offset(0, 7).Value
Cells(j, 9) = ActiveCell.Offset(0, 8).Value
Cells(j, 10) = ActiveCell.Offset(0, 9).Value
Cells(j, 11) = ActiveCell.Offset(0, 10).Value
Cells(j, 12) = ActiveCell.Offset(0, 11).Value
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

For x = x To 24

If Rows(x).Hidden = True Then
Rows(x).Hidden = False
End If
Next x

Rows("4:24").Hidden = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Thanks to WutUp WutUp for this tip on the forum.
Published by jak58. Latest update on December 7, 2011 at 09:48 AM by jak58.
This document, titled "Excel - Macro to detect and hide blank rows," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - Changing cell formula to text
Excel - Tasks to be performed automatically on particular dates