Make excel ignore “” columns when sorting?

[Ksilva]

Hi,

I have a multi-tab spreadsheet in which I pour in raw data to a raw data tab and in my main view tab I see the data points I care about from raw data in vLookup.

The problem is that the number of rows is inconsistent. One week I could paste 150 rows while the next I could paste 180. To be on the safe side on the main view I have formulas in 200 rows so that I don’t have to constantly drag the formula down to the last row. I added an iferror where errors would reflect “” aka blank so that it would be ignored. The issue I have is that when I sort any of the fields, depending on if I do it in ascending or descending order I will end up with a bunch of blank rows before getting my data. Is there a way to make excel disregard these rows since they are essentially blank but do have formulas in it?

ipen to other suggestions as well if there’s an easier way to do this.

Thanks!

 

[DSCfromCFA]

If you are using Sorting why not use Filtering as well and filter for blanks. Or

Have your IFERROR value something that would always sort it to the bottom of the list (this won't work if you sort both ways, but would if you consistently sort one way).

 

[Ksilva]

If you are using Sorting why not use Filtering as well and filter for blanks. Or

Have your IFERROR value something that would always sort it to the bottom of the list (this won't work if you sort both ways, but would if you consistently sort one way).

Hi!

i do have to sort both ways. When I do the filter, the next time I paste raw data that exceeds the current number of rows it continues to cut it off. Example: current data has 156 lines and I filter blanks. Next data set I paste in has 158 rows. When I paste that in, the system continues to keep the same rows hidden and only show 156 lines even though it should show 158.

Is there a way way to avoid this?

 

[DSCfromCFA]

You have to reset the filter before pasting in the next batch of data, as the filtering is essentially just hiding the rows with blanks in the filtered columns, and your paste is placing data in those hidden rows.

I suspect that the answer you need is in VBA, with a button to toggle hide/show rows with blanks. I am an absolute VBA novice - I can get a button to hide and show fixed rows, but my skills do not yet extend to how to make that dynamic based on a cell being blank, however, I'm sure a bit of searching will get you an answer.