VBA - Delete rows that DO NOT contain Bold font

[JamesonMH]

Hi there,

I know this is a pretty easy one, but can't get this to work. I have a bunch of data rows and if all the cells are not Bold in a row, then delete the row (just delete within data range, not entire row). Otherwise, if there is a cell in the row that is Bold, then leave row as is.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Store 3[/TD]
[TD]275
[/TD]
[/TR]
[TR]
[TD]Store 7[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]450[/TD]
[/TR]
</tbody>[/TABLE]

With above example, I'd only want the Store 3 & Store 7 rows to remain (excluding headers).

Thanks!
James

 

[Mentor82]

Hi again,
One more thing about my code I forgot to mention. If you have more than 2 columns you have to extend if from 2 to as many conditions as column you have.

 

[JamesonMH]

Sorry about the range/column omission. This works perfectly - thanks very much Rick! Question, what would I tweak in that code if I ever needed to delete the entire row instead of only what we're deleting right now?

'

 

[JamesonMH]

Replace what I highlighted in red above to this...

Rows(R).Delete xlShiftUp

Sorry Rick, I thought everything was working fine. But I'm noticing this code is requiring me to run it a half dozen times before it cleans up all the non-Bold rows.

My data range is A5:D96. Each time I run your code it shortens the range significantly (and correctly removes non-Bold rows), but it's just not everything on the first few runs (each time the range gets smaller until only bold rows remain).

How do I tweak this code so that it will accomplish the same goal, but all in one run?

Thanks
James

 

[Rick Rothstein]

Sorry Rick, I thought everything was working fine. But I'm noticing this code is requiring me to run it a half dozen times before it cleans up all the non-Bold rows.

My data range is A5:D96. Each time I run your code it shortens the range significantly (and correctly removes non-Bold rows), but it's just not everything on the first few runs (each time the range gets smaller until only bold rows remain).

How do I tweak this code so that it will accomplish the same goal, but all in one run?

Thanks
James

Silly mistake by me. Since we are deleting rows, the rows must be iterated from bottom to top... otherwise one of two adjacent rows to be deleted will be skipped when the first is deleted and the second one moves up to take its place... the loop then iterates thus skipping the one that moved up. Running the loop in reverse eliminates that from happening. To fix my code, change this line of code...

For R = StartRow To EndRow

to this...

For R = EndRow To StartRow Step -1

 

[JamesonMH]

Silly mistake by me. Since we are deleting rows, the rows must be iterated from bottom to top... otherwise one of two adjacent rows to be deleted will be skipped when the first is deleted and the second one moves up to take its place... the loop then iterates thus skipping the one that moved up. Running the loop in reverse eliminates that from happening. To fix my code, change this line of code...

For R = StartRow To EndRow

to this...

For R = EndRow To StartRow Step -1

Great, now it works flawlessly! I wouldn't have figured out that tweak you just made on my own. Thanks Rick for the speedy and informative reply.

James