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

  • Thread starter Thread starter Bob Benjamin
  • Start date Start date
B

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
 
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
 
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
 
Back
Top