Excel - Macro to detect and hide blank rows

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

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 Thanks to WutUp WutUp for this tip on the forum.

Spread the love

Leave a Comment