How do I delete rows and columns in With With End Loop?

[Bob Benjamin]

How do I have to modify the loop below in order
to delete columns A to H and rows 1 to 59 in wbk2?

With wbk2.Sheets(1)
.Cells.ClearFormats
.Cells(1, 1).Select
.Columns.ColumnWidth = 11
.Rows.RowHeight = 12
End With

Any help would be appreciated.

Bob

 

[shockley]

Bob,

Turn on the macro recorder and do the operation manually. Then look at the
recorded macro and you will see something like this:

Columns("A:H").Select
ActiveWindow.SmallScroll Down:=-3
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
ActiveWindow.SmallScroll Down:=36
Rows("1:59").Select
Selection.Delete Shift:=xlUp
Range("A1").Select

You can then edit these lines to this:

Columns("A:H").Delete Shift:=xlToLeft
Rows("1:59").Delete Shift:=xlUp

Within the With clause it will look like this:

With wbk2.Sheets(1)
.Cells.ClearFormats
.Cells(1, 1).Select
.Columns.ColumnWidth = 11
.Rows.RowHeight = 12
.Columns("A:H").Delete Shift:=xlToLeft
.Rows("1:59").Delete Shift:=xlUp
End With

HTH,
Shockley

 

[Tom Ogilvy]

A different interpretation:

by delete do you mean to clear?

With wbk2.Sheets(1).Columns("A:H")
.Cells.Clear
.EntireColumn.ColumnWidth = 11
End With
With wbk2.Sheets(1).rows("1:59")
.Cells.Clear
.EntireRow.RowHeight = 12
End With

or if you are talking the intersection (A1:H59)

With wbk2.Sheets(1).Columns("A1:H59")
.Cells.Clear
.EntireRow.RowHeight = 12
.EntireColumn.ColumnWidth = 11
End With

Replace .clear with .clearcontents to keep formats

if you actually mean delete

With wbk2.Sheets(1)
.Rows("1:59").EntireRow.Delete
.Columns("A:H").EntireColumn.Delete
End With

 

[Bob Benjamin]

Shockley and Tom,

Thank you for the help.
Regards,
Bob