Excel - Macro to detect and hide blank rows

January 2017


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 (ccm.net).