Excel – Macro to detect and hide blank rows

Last update on November 5, 2009 12:05 PM by jak58
Published by jak58

Excel – Macro to detect and hide blank rows




Issue



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.

Solution


You can try this:

Private Sub Macro1()

Dim j
Dim x
j = 2
x = 3

ActiveSheet.Unprotect
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
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("3:3").Select
Selection.Copy
Rows("2:2").Select
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:4").Select
Selection.EntireRow.Delete
Rows("4:24").Hidden = True

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

End Sub

Note


Thanks to WutUp WutUp for this tip on the forum.
They need your help