Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 9th 04, 03:15 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2004
Posts: 441
Default Bulk row deletion

Is there a way to use autofilter in code to show only certain rows with many
criteria?

For example, I need to display and delete only those rows conforming to all
of the following:

Column A: Cells like "###.###.####.######.###.##.###"
Columns C, D, E: Abs(C) + Abs(D) + Abs(E) = 0
Column F: < "U"

Once displayed, these rows will then be deleted. Your example code would be
most appreciated.

FYI: my original sheet contains 54,102 rows by 22 columns. About 29,000 rows
will be deleted. Thanks in advance.

  #2   Report Post  
Old November 9th 04, 05:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 67
Default Bulk row deletion

Hi Quartz,

You need to UNION your data ranges, look up UNION in the Excel Help, but the
code is something like

Set xls = Application.ActiveSheet
Set rgeSelection = Application.Selection

For Each rgeCell In rgeSelection
If xls.Cells(rgeCell.Row, rgeEnd.column + 2).Value Then
If rgeDelete Is Nothing Then
Set rgeDelete = rgeCell
Else
Set rgeDelete = Union(rgeDelete, rgeCell)
End If
End If
Next rgeCell

rgeDelete.EntireRow.Delete

The line "If xls.Cells(rgeCell.Row, rgeEnd.column + 2).Value Then" in my
sheet the last visible column plus two more columns, (a hidden column),
contained a value of True or False which permitted the user to delete the row
or not. You can change this to something like "If rgeCell.Row.Visible Then"

Hope that helps


"quartz" wrote:

Is there a way to use autofilter in code to show only certain rows with many
criteria?

For example, I need to display and delete only those rows conforming to all
of the following:

Column A: Cells like "###.###.####.######.###.##.###"
Columns C, D, E: Abs(C) + Abs(D) + Abs(E) = 0
Column F: < "U"

Once displayed, these rows will then be deleted. Your example code would be
most appreciated.

FYI: my original sheet contains 54,102 rows by 22 columns. About 29,000 rows
will be deleted. Thanks in advance.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bulk Discount Formula dgold82 Excel Discussion (Misc queries) 4 June 20th 20 09:57 PM
Bulk vlookup? Sarah Excel Worksheet Functions 2 January 24th 07 08:12 PM
Removing hyperlinks in bulk i-Zapp Excel Discussion (Misc queries) 1 October 26th 05 11:21 AM
Removing hyperlinks in bulk Elkar Excel Discussion (Misc queries) 1 October 26th 05 11:20 AM
Bulk Email Don Kline[_3_] Excel Programming 1 August 31st 03 04:27 PM


All times are GMT +1. The time now is 06:11 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017