Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop until
THANKS TO GARY BROWN for this awesome macro.
This is working but seems to need to be repeated to ensure that all hidden rows are being deleted. Just one more tweek and it will be perfect. How/where would you put a loop reference for just the Row (myrange) subroutine? '/======================================/ Public Sub FindHiddenAndLock() Dim mCell As Range, MyRange As Range Dim MyColumns As Range On Error GoTo exit_Sub Set MyRange = Range("A1:A20") Set MyColumns = Range("A:E") ---------THIS IS THE ROW SUBROUTINE--------------------- For Each mCell In MyRange If mCell.Rows.Hidden = True Then Application.DisplayAlerts = False mCell.EntireRow.Delete Application.DisplayAlerts = True End If Next mCell For Each mCell In MyColumns If mCell.Columns.Hidden = True Then Application.DisplayAlerts = False mCell.EntireColumn.Delete Application.DisplayAlerts = True End If Next mCell exit_Sub: On Error Resume Next Application.DisplayAlerts = True Set MyColumns = Nothing Set MyRange = Nothing Exit Sub End Sub '/======================================/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop until
Sub ABC()
Dim i as Long for i = 20 to 1 step -1 if rows(i).Hidden then rows(i).Delete Next for i = 5 to 1 step -1 if columns(i).Hidden then columns(i).Delete Next End Sub -- Regards, Tom Ogilvy "ttbbgg" wrote in message ... THANKS TO GARY BROWN for this awesome macro. This is working but seems to need to be repeated to ensure that all hidden rows are being deleted. Just one more tweek and it will be perfect. How/where would you put a loop reference for just the Row (myrange) subroutine? '/======================================/ Public Sub FindHiddenAndLock() Dim mCell As Range, MyRange As Range Dim MyColumns As Range On Error GoTo exit_Sub Set MyRange = Range("A1:A20") Set MyColumns = Range("A:E") ---------THIS IS THE ROW SUBROUTINE--------------------- For Each mCell In MyRange If mCell.Rows.Hidden = True Then Application.DisplayAlerts = False mCell.EntireRow.Delete Application.DisplayAlerts = True End If Next mCell For Each mCell In MyColumns If mCell.Columns.Hidden = True Then Application.DisplayAlerts = False mCell.EntireColumn.Delete Application.DisplayAlerts = True End If Next mCell exit_Sub: On Error Resume Next Application.DisplayAlerts = True Set MyColumns = Nothing Set MyRange = Nothing Exit Sub End Sub '/======================================/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |