Deleting Cells that have a value in them

[shipleyscott]

I have a data set that this the ouput from a 2-d flow model. In places where the river is not deep enough I get a result of -9999. At some flows the river is largely dry and most of what I get are -9999s. The output is a 2000 x 2000 (or so) matrices that are mostly -9999. What I want to do is get rid of all the -9999s as well as the cells that they reside in and move the remaining data left (so that it is up against the axis with meaningful data starting in column A (I realize that this will get rid of islands, but I think this is probably ok. I’d keep the islands if I could.

This will have the effect of putting all of the usable data into a matrix that is more like 100 x 2000 and will allow me to conditionally format the data to do my analysis. I can also then present it into a usable fashion.

Here’s how I will use the data:

I will copy and paste the data from a worksheet into my analysis worksheet. I will have a tab for depth and a separate tab for velocity at each of 100 cfs, then one for 150 cfs, then one for 250 cfs, etc.

Then I will have separate pages already set up to analyze the results from my raw data pages. These are the ones that only want to reference meaningful data. As I iterate my design I will continually replace the data in the raw data pages. This means that I only want to format the data after it is referenced into another sheet (I think).

Within these results I will conditionally format the cells to show whether fish passage is feasible or not. The hope is that fish passage will be possible at some point for each cross section. The definition for passable will be to evaluate the existing conditions at the site and find the cross-section with the most prohibitive velocity values across the river for all nodes with a depth greater than .6 feet. This will be our comparison value.

Any ideas on how to cull the data set in a mass produced way?

 

[Rick Rothstein]

I have a data set that this the ouput from a 2-d flow model. In places where the river is not deep enough I get a result of -9999. At some flows the river is largely dry and most of what I get are -9999s. The output is a 2000 x 2000 (or so) matrices that are mostly -9999. What I want to do is get rid of all the -9999s as well as the cells that they reside in and move the remaining data left (so that it is up against the axis with meaningful data starting in column A (I realize that this will get rid of islands, but I think this is probably ok.

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub RemoveMinus9999s()
  With ActiveSheet.UsedRange
    .Replace -9999, "#N/A", xlWhole, SearchFormat:=False, ReplaceFormat:=False
    .SpecialCells(xlConstants, xlErrors).Delete xlToLeft
  End With
End Sub[/td]
[/tr]
[/table]

 

[shipleyscott]

Thanks Rick:

I ran the macro and it went into an endless loop that I had to quit out of. I'm not sure what happened. The macro immediately replaced all the -9999s with #N/As but did not delete the cells that the N/As were in and got sort of hung there at that point. I'm playing with it now, but not having much luck...

 

[Rick Rothstein]

I ran the macro and it went into an endless loop that I had to quit out of. I'm not sure what happened. The macro immediately replaced all the -9999s with #N/As but did not delete the cells that the N/As were in and got sort of hung there at that point. I'm playing with it now, but not having much luck...

Hmm, my code does not use a loop, so it cannot get stuck in an endless one. Something else must be wrong. What version of Excel are you using?

 

[shipleyscott]

Hmm, my code does not use a loop, so it cannot get stuck in an endless one. Something else must be wrong. What version of Excel are you using?

I have Excel 2013.