Selecting a range to delete

[John Keith]

After sorting a large amount of data (approaching 62,000 rows) I would
like to select a range that includes all the rows that contain the
value "Alt" in column G and delete the entire selection (complete
row). What is the fastest way to determine how to specify this range
area? I originally was doing this in a for loop and the data set has
grown to the point where the speed of the loop is just too limiting.
If there is a faster approach than the sort option I'm think about
please offer your suggestion!

Thanks


John Keith
(e-mail address removed)

 

[Gary''s Student]

1. turn on AutoFIlter on column G
2. select the rows containing "Alt"
3. delete the visible rows

This can be done easily either manually or with VBA.

 

[John Keith]

1. turn on AutoFIlter on column G
2. select the rows containing "Alt"
3. delete the visible rows

This can be done easily either manually or with VBA.

Gary's Student,

I thought about this but I did not try it because the data set has
numerous locations with blank cells and I think the autofilter might
stop before all the rows are filtered. Am I wrong or is there some way
to overcome this limitation?

Thanks



John Keith
(e-mail address removed)

 

[Don Guillett]

Autofilter should show 1000 UNIQUE items. So, if you have 10,000 and 1200
are in your filter, you don't get all.

 

[excelent]

This one delete entirerow where Alt is in col G

Sub DelRows()
Dim t, rk
Application.Calculation = xlCalculationManual
rk = Cells(65500, "G").End(xlUp).Row

For t = 1 To rk
If Cells(t, "G") <> "Alt" Then Cells(t, "CV") = 1
Next

Range("CV1:CV" & rk).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("CV1:CV" & rk) = ""
Application.Calculation = xlCalculationAutomatic
ActiveCell.Select
End Sub


"John Keith" skrev:

 

[Gary''s Student]

You are correct to be concerned. You need to tell the AutoFilter to cover
the entire range including any embedded blank cells. In the code below:

1. rG is set to cover the portion of column G that should be examined
2. the filter is applied to the ENTIRE column
3. we use SpecialCells to locate the visible cells in the filtered data
4. we delete the rows containing the visible cells
5. we cleanup by removing the filter

Sub RowKiller()
'
' gsnuxx
'
Dim rVis As Range, n As Long
Dim rG As Range
n = Cells(Rows.Count, "G").End(xlUp).Row
Set rG = Range("G2:G" & n)
Columns("G:G").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Alt"
Set rVis = Intersect(rG, ActiveSheet.Cells.SpecialCells(xlCellTypeVisible))
rVis.EntireRow.Delete
Range("G1").Select
Selection.AutoFilter
End Sub

 

[John Keith]

This one delete entirerow where Alt is in col G

excelent,

I will try your technique after I get some shut eye. Thank you fo rthe
suggestion.


John Keith
(e-mail address removed)

 

[John Keith]

You are correct to be concerned. You need to tell the AutoFilter to cover
the entire range including any embedded blank cells. In the code below:

Thank you for the feedback and confirmation to be concerned.

I did play a little with autofilter manually just to see what might
happen. I acutally have 6 different terms in the column G that I want
to filter out and my observations were:

Even autofilter was slow, it took 2-3 minutes to select the filtered
rows and then several moments to delete the resulting selection.

And on one of the terms autofilter failed (I forget the error message,
it was LATE last night) but I deduced the failure was a result of the
size of the worksheet and the size of the of the dataset that was
selected.

I will try your suggestion later and see how it works.

Large datasets are a PAIN!


John Keith
(e-mail address removed)

 

[Don Guillett]

Or filter for blanks........

 

[John Keith]

After sorting a large amount of data (approaching 62,000 rows) I would
like to select a range that includes all the rows that contain the
value "Alt" in column G and delete the entire selection (complete
row). What is the fastest way to determine how to specify this range
area? I originally was doing this in a for loop and the data set has
grown to the point where the speed of the loop is just too limiting.
If there is a faster approach than the sort option I'm think about
please offer your suggestion!

What I finally did:

sorted the data by column G
search for the forst and last row with instance "Alt"
selected the range inclusive of these rows
deleted the selection

The end result works well and is fast enough for me need.

Thanks for the tips


John Keith
(e-mail address removed)