VBA - Delete rows that DO NOT contain Bold font

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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.

NameCode
Store 1500
Store 3275
Store 7480
Store 2450

<tbody>
</tbody>

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

Thanks!
James
 
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.
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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?

'
 
Last edited:
Upvote 0
Replace what I highlighted in red above to this...
Code:
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
 
Upvote 0
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.:oops: 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
 
Upvote 0
Silly mistake by me.:oops: 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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top