Delete rows in a sequence

[MostlyClueless]

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

 

[Don Guillett]

Try this idea
good
good

bad
bad
bad
bad
good
good
bad
bad
bad
bad
good
good
bad
bad
bad
bad
good
good

Sub dobadrows()
'Sheets("sheet1").Rows("2:100").Copy Range("a2")
lr = Cells(Rows.Count, "a").End(xlUp).Row
'MsgBox br
On Error GoTo nomo 'Resume Next
For i = lr To 2 Step -3
lr = Cells(Rows.Count, "a").End(xlUp).Row
br = Columns("A").Find(What:="bad", After:=Cells(lr, "a"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row - 3
Rows(br).Resize(4).Delete
'MsgBox i
Next i
nomo:
End Sub

 

[Rick Rothstein]

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 Rothstein]

The 12 bad rows don't always start on the same row among files, but
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.

 

[MostlyClueless]

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.

 

[Rick Rothstein]

I'm glad the macro works for you. I think we may be able to modify the macro
to find the start row without you having to select it, but I need you to
clarify something for me. Is blank row in front of the first bad row the
first blank row in your data? That is, I'm guessing you have one or more
header rows followed immediately by data-filled rows (with no hidden, empty
rows), then you come to a completely blank row and then your come to your
first piece of bad data... is that correct? When you said "but there is one
other empty row between the first and the last", do you mean there is a
blank row in the middle of your data somewhere? If yes, is this blank "in
between" row **always** there in every set of bad data (and I am assuming it
doesn't affect the 12/52 total of repeating bad data sets)? Finally, exactly
what do you mean by "the last of the 12 rows is also empty"... my macro
assumed the bad data went to the end of entire set of data... are you saying
you have "good" data following the bad data? If so, I think I'll have to
account for that because if you trailing "good" data ever takes up more than
64 rows, my macro could start deleting parts of it as well.

--
Rick (MVP - Excel)


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.

 

[Clif McIrvin]

PMFJI ...

Just jumping in here to say "Thank You!" to Rick for showing me
something new:

ActiveSheet.Rows(X).Resize(12).Delete

I noticed the .resize in here and was curious what it was doing ....

Just a couple days ago I was wondering how to operate on multiple rows
.... something like
" Rows("32:45") " when what I had was a numeric value ... and here
it is!

Thanks, Rick!

 

[MostlyClueless]

Yeah, it works great. Thank you so much!

No, the blank row in front is not the first blank row in the data.

Yes, there is a blank in-between row in every set of bad data. The
sequence is:
blank row
variable number of non-blank rows with data I don't want
blank row
row of data I don't want
blank row

What it comes down to is the spreadsheet is generated via a print
spool, and page headers are automaticaly put into the data file. There
appears to be no way in the source application to supress the page
headers when generating the output.

So far, I have not encountered any output that it has not worked
correctly on, I just change the "(12)" to the appropriate number of
rows to delete and I'm golden. I will keep in mind there can be a
problem if there is more than 64 rows of good data, but I don't think
that will be encountered.

The only "issue" I've encountered is the macro does not delete the
very last occurance of the bad data. But considering how much time
I've saved, the extra 2 seconds to manually delete those rows is
nothing.

Have I said thanks? This rocks!

Dan

 

[Rick Rothstein]

Your example layout didn't show data you *do* want. I'm guessing you meant
this layout (although I'm not completely sure of when the 64 total rows
start and end in this layout... did I get it right?)...

blank row
variable data I don't want (Start of 64 total rows)
blank row
variable data I do want
blank row (End of 64 total rows)
variable data I don't want (Start of 64 total rows)
blank row
variable data I do want
blank row (End of 64 total rows)
12 blank rows
more data

I have code that will let me locate where the first block of 12 blank rows
are located; so, assuming there are **never** 12 blank rows *before* the
data you want to process with the macro, I can calculate the point at which
I can back off 64 rows of data at a time until I find some kind of indicator
for the start of data that needs processing... I just need you to tell me
something that is *always* in your data that precedes the data to be
processed. I had hoped it would be the first blank row, but you have told me
it isn't. Is it perhaps the Nth (1st, 4th, etc.) blank row? Or maybe their
is a constant type of header text that is in front of it? Maybe a line of
dashes? What about the blank row in front of the blank row that starts the
data you want to process... is it perhaps **always** not 64 rows in front of
the blank row that starts the data you want to process? I'm looking for
something regular about the layout that I can calculate up to or away
from... if you can tell me that, I can write code to find it and automate
the process for you. If your data is not confidential, maybe you could send
me a copy of one of your worksheets (tell me what row the data you want to
process is on) so I can see if I recognize anything that could be used for
what I have in mind. If that would be alright with you, just remove the
NO.SPAM stuff from my email address and send it to me.

--
Rick (MVP - Excel)


Yeah, it works great. Thank you so much!

No, the blank row in front is not the first blank row in the data.

Yes, there is a blank in-between row in every set of bad data. The
sequence is:
blank row
variable number of non-blank rows with data I don't want
blank row
row of data I don't want
blank row

What it comes down to is the spreadsheet is generated via a print
spool, and page headers are automaticaly put into the data file. There
appears to be no way in the source application to supress the page
headers when generating the output.

So far, I have not encountered any output that it has not worked
correctly on, I just change the "(12)" to the appropriate number of
rows to delete and I'm golden. I will keep in mind there can be a
problem if there is more than 64 rows of good data, but I don't think
that will be encountered.

The only "issue" I've encountered is the macro does not delete the
very last occurance of the bad data. But considering how much time
I've saved, the extra 2 seconds to manually delete those rows is
nothing.

Have I said thanks? This rocks!

Dan

 

[Rick Rothstein]

Until you answer the questions in my other post, here is a modified macro
that does two things. First, because you said in an earlier response that
"the last of the 12 rows is also empty", I changed the code to automatically
stops processing data when it reaches the first block of 12 blank rows after
the start cell (that you select before running the macro), so you won't have
to worry about accidentally deleting rows of good data at the end of your
data sheet. Second, because you said "I just change the "(12)" to the
appropriate number of rows to delete and I'm golden" in your last post, I
modified the code so that it asks you how many bad rows of data to process
(this way, you won't have to manually change the number each time).
Hopefully, you like these changes.

Sub DeleteTwelveBadRows()
Dim X As Long, BeginRow As Long, FinalRow As Long
Dim LastRow As Long, BadRowGrouping As Long
Dim Addresses() As String
BadRowGrouping = InputBox("How many bad rows need to be removed?")
BeginRow = Selection(1).Row
Addresses = Split(ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks). _
EntireRow.Address(0, 0), ",")
For X = 1 To UBound(Addresses)
If ActiveSheet.Range(Addresses(X)).Rows.Count >= 12 Then
LastRow = Split(Addresses(X), ":")(0)
End If
Next
FinalRow = BeginRow + 64 * ((LastRow - BeginRow + 1) \ 64) - 64
For X = FinalRow To BeginRow Step -64
ActiveSheet.Rows(X).Resize(BadRowGrouping).Delete
Next
End Sub

--
Rick (MVP - Excel)


Yeah, it works great. Thank you so much!

No, the blank row in front is not the first blank row in the data.

Yes, there is a blank in-between row in every set of bad data. The
sequence is:
blank row
variable number of non-blank rows with data I don't want
blank row
row of data I don't want
blank row

What it comes down to is the spreadsheet is generated via a print
spool, and page headers are automaticaly put into the data file. There
appears to be no way in the source application to supress the page
headers when generating the output.

So far, I have not encountered any output that it has not worked
correctly on, I just change the "(12)" to the appropriate number of
rows to delete and I'm golden. I will keep in mind there can be a
problem if there is more than 64 rows of good data, but I don't think
that will be encountered.

The only "issue" I've encountered is the macro does not delete the
very last occurance of the bad data. But considering how much time
I've saved, the extra 2 seconds to manually delete those rows is
nothing.

Have I said thanks? This rocks!

Dan