Deleting rows keeps rows with shaded cells

anaconda777

New Member
Joined
Nov 11, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi all,

I used a code from this message board to delete rows where column C is blank, however it is keeping the rows where the cell is shaded with a color.

Clearing all colors prior to running the macro also does not seem to work.

Here is what I used. Any tips appreciated. PS - I am brand new at this.
 

Attachments

  • column C.jpg
    column C.jpg
    56.5 KB · Views: 11

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
So, you want a script to see if the cell in column C is empty and if so delete this row is that correct?
And by empty, I mean there is no value in column C
And column A will always have data is that correct? So, the last row in this worksheet with data will Have data in column A is that correct? If not what column will always have some data to last row with data?
 
Upvote 0

anaconda777

New Member
Joined
Nov 11, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So, you want a script to see if the cell in column C is empty and if so delete this row is that correct?
And by empty, I mean there is no value in column C
And column A will always have data is that correct? So, the last row in this worksheet with data will Have data in column A is that correct? If not what column will always have some data to last row with data?

Thanks for the reply.

Yes. And yes, no value in column C.
Column A will not always contain data. In fact, none of the columns will always contain data on every row, some rows are totally blank and some are partially blank.

Lets say there are 10 columns and 50 rows, about 15 of those rows will have data in column C. The final result should consist of column C‘s 15 rows and the associated data in columns F and I. All of the other rows can be deleted. However, the cells in column C with no data but have color shading stick around. The columns I know how to delete with Record macro.
 
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
Try this:
VBA Code:
Sub Delete_Rows_If_C_Is_Blank()
'Modified 6/20/2022  10:50:49 PM  EDT
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.UsedRange.Rows.Count
Dim i As Long

For i = ans To 1 Step -1
    If Cells(i, 3).Value = "" Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution

anaconda777

New Member
Joined
Nov 11, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi, thank you for the code. I am having difficulty incorporating that within my current code i.e. either some text turns red and/or there is an error about the sub.

The full code is attached. I tried to remove the circled part which was leaving the shaded cells and put in your code but I believe there may be an issue with two End subs back to back? I deleted one but still an issue.
 

Attachments

  • full code June 23.jpg
    full code June 23.jpg
    103.1 KB · Views: 1
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
My code should do the job you asked for.

Why do you think you need to use your code which does not work along with my code?

And if you have code, you should post the code here for me to see and not post an image of the code
 
Upvote 0

anaconda777

New Member
Joined
Nov 11, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
On its own, what you provided has worked. Thank you so much. This puts me way ahead of where I was as that was the most onerous part.

When trying to incorporate it into my previous recorded code it was an issue. It could be because the columns changed earlier in the code versus what the code used later.
 
Upvote 0

anaconda777

New Member
Joined
Nov 11, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I will try to put it all together again beginning with your code and then the mostly inconsequential formatting and learn how to post the code and not an image. Thanks.
 
Upvote 0

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,342
Office Version
  1. 2021
Platform
  1. Windows
On its own, what you provided has worked. Thank you so much. This puts me way ahead of where I was as that was the most onerous part.

When trying to incorporate it into my previous recorded code it was an issue. It could be because the columns changed earlier in the code versus what the code used later.
Using the Recorder is OK to try and learn Vba or for simple scripts but is not a good way to write a script. You should explain in detail what you're trying to do with specific details.

And anytime you have code it's best to post the code here in the forum and not an image of the code
 
Upvote 0

Forum statistics

Threads
1,186,314
Messages
5,957,141
Members
438,289
Latest member
donnabee

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
Top