Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
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 - gsnu200902 "John Keith" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
On Sat, 5 Sep 2009 13:22:02 -0700, Gary''s Student
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
Autofilter should show 1000 UNIQUE items. So, if you have 10,000 and 1200
are in your filter, you don't get all. -- Don Guillett Microsoft MVP Excel SalesAid Software "John Keith" wrote in message ... On Sat, 5 Sep 2009 13:22:02 -0700, Gary''s Student wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
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.Delet e Range("CV1:CV" & rk) = "" Application.Calculation = xlCalculationAutomatic ActiveCell.Select End Sub "John Keith" skrev: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
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 -- Gary''s Student - gsnu200902 "John Keith" wrote: On Sat, 5 Sep 2009 13:22:02 -0700, Gary''s Student wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range to delete
On Sun, 6 Sep 2009 07:33:01 -0700, Gary''s Student
wrote: 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 we 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting a small range of cells to delete | Excel Programming | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Selecting every other row to delete or move | Excel Discussion (Misc queries) | |||
Delete cells without selecting? | Excel Programming | |||
Selecting a group of worksheets to delete | Excel Programming |