How do I select multible non-contiguous rows by criteria?

ECF1956

Board Regular
Joined
Mar 2, 2005
Messages
54
I have a spreadsheet that has 8 columns with data fields in columns. I want to select the rows in VBA that have a null value in the 5th column. I then want to copy them. Can anyone help? :rolleyes:
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
How about:

Code:
Range("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Copy

Just change the range to the column you're looking at. HTH
 
Upvote 0
This does not solve the problem

I need to select more than one non-contiguous row when Column 5 is blank.
 
Upvote 0
Thanks

TAZ your code worked - But it also copies all rows after those that have data. What is the best way to filter those out?
 
Upvote 0
I see why. You could do it this way:

Code:
Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Copy

so it looks at Column A to find the last row of data. You could also use Autofilter or Advanced Filter.
 
Upvote 0
Thanks ever so much

I am a novice in VBA programming. The help you guys give me makes my life so much easier. I had the autofilter solution but your code is much cleaner. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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