Link data to another Excel file excluding blanks

[rgreen511]

I have an Excel file that links to another file and grabs data using a simple link, no macros.

The entries are of the format (ex):

11/27/07 12.3

Several of the entries are blank, and the blanks screw up my equations that are using the data (calculating moving ranges, etc.).

I'm looking for a way to eliminate these blank lines, but still maintain previous changes I've made, such as excluding points, or changing the values of points. I thought of pulling the data into a different sheet, eliminating the blanks, and then copying the cleaned up version to the main sheet. However, this copy and paste will overwrite the previously mentioned modifications.

I'm not afraid to use macros or VB, I just couldn't think of an easy way to do this. Thanks.

 

[Bong]

Can you preceed your equations with a condition check, like
if(A1="",,<your equation>)?

_________________
Bob Rashkin

 

[rgreen511]

Even more critical than the equations though is a graph that is showing the blanks as zeros.

 

[Bong]

So, do you just want to delete the rows that contain the blank cells?

If so, there are several examples here; the trick is to work from the bottom up.

_________________
Bob Rashkin

 

[JoeF]

I'm not a programmer, but I have some suggestions. I'm not sure what type of formulas you are using or how you are linking, but if your chart is picking up the blanks, are you sure they are truly blanks? It might look like the cell is blank, but you could still have space characters or other characters in the cells. If so, the solution could be as simple as selecting and copying any characters that are in those "blank" cells and doing a find/replace for the string of characters. Since you have copied the characters, paste them into the "Find What:" field in the Find/Replace prompt and just replace with noting to clear those cells out.

Another solution would be to sort out the blanks and copy/paste your data to a fresh new sheet. You can have a macro do this if you want to automate it. Have the macro sort the data, then select the rows that aren't blank using code like below and then paste it into the new sheet.
Dim RowCount
RowCount = Application.CountA(Range("A:A"))
Range("A1:N" & RowCount).Select
Selection.Copy