Stop Macro if blank cells are within certain columns

Hi,

I have some code which extracts data from a certain range and pastes the data into another excel workbook. Within that range there are certain columns which are required to have data in them. If not, the macro will stop and a pop up box will tell the user which column requires data. The code below shows how I find the range to extract:

Please Login or Register  to view this content.

I need to search columns B,D,I,J and P to ensure these fields are completed.

Any ideas?

I've adapted the code to find a blank cell within that range

Range("A171").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select

But I want it to ignore blank cells unless there are in Columns B,D,I,J and P and stop the macro with a pop-up box.

Thanks but I need to use the code:

Please Login or Register  to view this content.

to find the full range then look in the columns for blanks. So close...

not quite sure I understand the use for your code..
it simply finds the first occupied cell in column A after row 171, so why bring in all the other cells?
you could simplify by selecting A171 then do an xlDown.

anyway.. based on that, I understand that what you want is to check for unused cells in a cellrange from A171 down to the first colum in A with value.; within other columns.. correct?

Modified the code for that use as follows:

Please Login or Register  to view this content.

note: if this is not what you want, please attach an example workbook so it's easier to understand your wishes.

That's exactly what I need but the code is telling me there is no data in column b when there is?? Also, would this work if the cells had formula in them?

forgot that the range had changed..
this should work.

(Note: if there are blank cells in row 171 it will ignore it.)

Please Login or Register  to view this content.

Winner - works a treat!!!

Thanks!!

good.

please mark thread as solved

This code is ignoring formula that equals ""? I want the code to see these as blanks. Please help.