Delete a searched range

[Zurn]

I search first my range of rows, then I want to delete all the rows

Sub DeleteAll()

Dim firstCAP As Range
Dim lastCAP As Range

'set START DATA_collect
Sheets("DATA_collect").Activate
Range("N6").Activate 'start search here at DATA_collect
firstCAP = ActiveCell.Rows
'goto first cell that is empty
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(-1, 0).Activate
lastCAP = ActiveCell.Rows

Rows("firstCAP:lastCAP").Select
Selection.Delete Shift:=xlUp


End Sub

I get a 'Run time error 91'
Object variable or with block variable not set.

Where can I change the code in order to get it work?

[jindon]

Hi

have tried
Dim firstCAP As Long
Dim lastCAP As Long

Activecell.Row

not Rows


rgds,
jindon

[Zurn]

Yes, I tried it...then I get the Un Time Error 13:Type Mismatch

This on the line: Rows("firstCAP:lastCAP").Select


I do not understand the error, normally it has to work with the .Row

[jindon]

Hi

could be

Rows(firstCAP & ":" & lastCAP).Select

variables shold be Long

jindon

[Zurn]

Jindon, thx!

My nightmare is over!


I would like to know why only the ":" needs to be with ", not the entire range...is this evident?

[jindon]

Hi

Because they are the variavles that you declaired in the dim statement.

rgds
jindon