Clearing used range?

  • Thread starter Thread starter jodleren
  • Start date Start date
J

jodleren

I want to clear data, meaning that I delete anything below row 4, en
after say "D"

Right now it jumps to EE10000 as the last cell, eg highest cell.

This code does not work

ws.Rows("200:65536").EntireRow.Delete
ws.Columns("Z:IV").EntireColumn.Delete
ws.UsedRange ' invalid use of property?

Sonnich
 
Hi,

Dim ws As Worksheet

Set ws = Sheets("Sheet2")

'dispense with EntireRow and EntireColumn
'Have already said Row and Column which
'means Entire Row or Entire column.
ws.Rows("200:65536").Delete
ws.Columns("Z:IV").Delete

'Need to do something with UsedRange
'Your example is a bit like entering
'ws.Range ("A1:D10")

'ws.UsedRange ' invalid use of property?

'**********************************

alternative method

Dim ws As Worksheet
Dim lastRow As Long

Set ws = Sheets("Sheet2")

'Find last row of used range
With ws.UsedRange
lastRow = .Rows(.Rows.Count).Row
End With

ws.Rows(200 & ":" & lastRow).Delete

ws.Columns("Z:IV").Delete
 
Back
Top