Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to modify macro please
I wish to use the following well publicized macro to remove blank rows in a
worksheet. However I wish to tell it to ignore rows 50 to 55 inclusive. How would I modify it to achieve this? Sub DeleteUnused() Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range For Each wks In ActiveWorkbook.Worksheets With wks myLastRow = 0 myLastCol = 0 Set dummyRng = .UsedRange On Error Resume Next myLastRow = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row myLastCol = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column On Error GoTo 0 If myLastRow * myLastCol = 0 Then .Columns.Delete Else .Range(.Cells(myLastRow + 1, 1), _ .Cells(.Rows.Count, 1)).EntireRow.Delete .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete End If End With Next wks Thanks Brian Tozer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to modify macro please
One way:
I presume this is just to preserve formatting, right (since there would be no values to save if myLastRow was < 50)? Public Sub DeleteUnused() Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range For Each wks In ActiveWorkbook.Worksheets With wks myLastRow = 0 myLastCol = 0 Set dummyRng = .UsedRange On Error Resume Next myLastRow = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row myLastCol = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column On Error GoTo 0 If myLastRow * myLastCol = 0 Then .Columns.Delete Else If myLastRow < 55 Then .Range(.Cells(56, 1), _ .Cells(.Rows.Count, 1)).EntireRow.Delete If myLastRow < 50 Then _ .Range(.Cells(myLastRow + 1, 1), _ .Cells(49, 1)).EntireRow.Delete Else .Range(.Cells(myLastRow + 1, 1), _ .Cells(.Rows.Count, 1)).EntireRow.Delete End If .Range(.Cells(1, myLastCol + 1), _ .Cells(1, .Columns.Count)).EntireColumn.Delete End If End With Next wks End Sub Note that if the last row is, say 35, that the formatting of rows 50:55 will be in 36:41 after the macro runs. In article , "Brian Tozer" wrote: I wish to use the following well publicized macro to remove blank rows in a worksheet. However I wish to tell it to ignore rows 50 to 55 inclusive. How would I modify it to achieve this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to modify macro please
J.E. McGimpsey wrote:
One way: Thanks very much J.E. Off to clear the log-jam. Brian Tozer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to modify my macro ... | Excel Discussion (Misc queries) | |||
Modify A Macro | Excel Worksheet Functions | |||
Modify a Macro | Excel Worksheet Functions | |||
Modify macro to copy to next available row | Excel Discussion (Misc queries) | |||
How to modify a copy macro | Excel Worksheet Functions |