Macro: Detecting blank cells that contain formulas

[JeffFinnan]

Hi there,

I have a sheet in a workbook that lists data from another sheet depending the presence of data or not.

=IF(ISBLANK(inventSouth!D3),"",inventSouth!D3), =IF(ISBLANK(inventSouth!E3),"",inventSouth!E3), etc
=IF(ISBLANK(inventSouth!D4),"",inventSouth!D4), =IF(ISBLANK(inventSouth!E4),"",inventSouth!E4), etc
Many more rows down

=IF(ISBLANK(PendEmergSouth!D3),"",PendEmergSouth!D3), =IF(ISBLANK(PendEmergSouth!E3),"",PendEmergSouth!E3), etc
=IF(ISBLANK(PendEmergSouth!D4),"",PendEmergSouth!D4), =IF(ISBLANK(PendEmergSouth!E4),"",PendEmergSouth!E4), etc
Many more rows down.

What I do is select the non-blank cells and copy the results manually and paste as test into another sheet in the workbook. I copy the inventSouth first and paste, then go down to PendEmergeSouth section next, copy and paste. I want the data as text because I later do some sorting.

I would like to build a macro that does the same. Copies the inventSouth until it is blank and pastes into other workbook, then goes down to PendEmergSouth and does likewise.

Where I need help in particular, is detecting the blank cells in the macro even though they contain formulas.

Thanks,
Jeff

PS. I do Marine Turtle Patrol. This for a sheet I print out that lists the pending emergences (nests that are expected to have their hatchlings emerge) and pending inventories (nest that we dig out and inventory three days after emergence.) I sort the list by latitude along the beach.

 

[Anthony47]

You migh use the following code to detect the last non empty cell in a range:

LastR = Evaluate("MAX(IF('Sheet2'!H1:H100<>"""",ROW('Sheet2'!H1:H100),""""))")

This will return in variable LastR the last populated row in 'Sheet2'!H1:H100

The structure of your data is too vague to allow for a more complete answer.

If you do not succede in adapting that line in your code then try recording a macro while you manually do a copy:
-select the first range
-go to the new sheet, select the destination cell and paste-special/values
-select the second range and repeate is cpy & paste-special/values
Stop recording the macro and publish the code you obtaining, and we'll see to modify it and adapt to all the conditions.

Bye