Re: Delete rows in a sequence
- From: MostlyClueless <casabre@xxxxxxxxx>
- Date: Tue, 2 Jun 2009 06:55:36 -0700 (PDT)
Thank you so much Rick, it worked perfectly!
I can recognize the first bad row as it is an empty row
The last of the 12 rows is also empty, but there is one other empty
row between the first and the last. If that makes sense.
On Jun 1, 1:51 pm, "Rick Rothstein"
<rick.newsNO.S...@xxxxxxxxxxxxxxxxxx> wrote:
The 12 bad rows don't always start on the same row among files, but
once the sequence starts, it is consistent for the entire sheet.
Ideally, I'd like to be able to place my cursor on the first row that
needs to be deleted, then have a macro do the rest.
Just out of curiosity, how do you recognize which row your bad data starts
on? If you can describe, in detail, how you recognize this first row of bad
data (or, perhaps alternately, the last row of "good" data before the first
row of bad data), then maybe we can include code to duplicate that process
so that you wouldn't have to select a cell on that row before running the
macro.
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.newsNO.S...@xxxxxxxxxxxxxxxxxx> wrote in message
news:OHYjYiv4JHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
Make any cell on the first "Bad" row of data active and then run this
macro...
Sub DeleteTwelveBadRows()
Dim X As Long, BeginRow As Long, FinalRow As Long, LastRow As Long
BeginRow = Selection(1).Row
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
FinalRow = BeginRow + 64 * ((LastRow - BeginRow + 1) \ 64) - 64
For X = FinalRow To BeginRow Step -64
ActiveSheet.Rows(X).Resize(12).Delete
Next
End Sub
NOTE: Be *very* careful about activating a cell on the bad row... actions
performed by VB code **cannot** be undone.
Oh, and yes, that backward slash is correct... do *not* change it to a
forward slash... the backward slash is an operator that performs integer
division.
--
Rick (MVP - Excel)
"MostlyClueless" <casa...@xxxxxxxxx> wrote in message
news:36569b27-7b8e-4d58-86f2-d5bb58babe76@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
It seems like it should be simple to do, but I can't figure it out, so
I'm asking for help.
I've got multiple Excel files with at least 20,000 rows of data. In
each of the files, the data follows a specific sequence.
There will be 12 rows of data that need to be deleted, then 52 rows of
good data I don't want to touch, then 12 rows of data that need to be
deleted, then 52 rows of good data, etc down to the bottom of the
sheet.
The 12 bad rows don't always start on the same row among files, but
once the sequence starts, it is consistent for the entire sheet.
Ideally, I'd like to be able to place my cursor on the first row that
needs to be deleted, then have a macro do the rest. Is that possible?
I tried recording a macro, but had bad results when trying to use the
macro across sheets (it would delete the wrong rows).
Thank you for any help or suggestions!
Dan
.
- Follow-Ups:
- Re: Delete rows in a sequence
- From: Rick Rothstein
- Re: Delete rows in a sequence
- References:
- Delete rows in a sequence
- From: MostlyClueless
- Re: Delete rows in a sequence
- From: Rick Rothstein
- Re: Delete rows in a sequence
- From: Rick Rothstein
- Delete rows in a sequence
- Prev by Date: Importing external data when you have Internet Explorer 8
- Next by Date: Re: Importing external data when you have Internet Explorer 8
- Previous by thread: Re: Delete rows in a sequence
- Next by thread: Re: Delete rows in a sequence
- Index(es):
Relevant Pages
|
