Parsing data ranges and pulling out cells with a value

[systematic]

Hi folks,

Trying to find the best solution to this problem and assume that a programmatic approach is the way to go. A worksheet I am trying to extract data from is a complete mess - unfortunately I am locked into the current layout.

Columns B, C & D contain HR information that is important.

Running across the spreadsheet I need to analyse columns Q&R, U&V, Y&Z and so on, each containing around 200 rows of potential data.

The cells I am analysing are mostly empty. However some contain a value. If the cell contains a value, I want to get that value and corresponding data from rows B,C & D then paste it as a single line in a different worksheet.

I could develop a loop that will go through each cell and pull the data out - however using offset this would turn out very slow and clunky (approx 35,000 cells).

Any suggestions for a better way to do this?

Cheers

systematic

[dflak]

I can think of a quick and dirty, one-off way of doing this with a helper column

Make a column with the formula =COUNTA(Q2,R2,U2,V2,Y2,Z2) > 0. This is true is any of the cells have data or false if they are all blank.

Filter on this column. Copy and Paste.

Make sure these cells are truly blank and don't have formulas that equate to "". If they do have formulas try (LEN(Q2) + LEN(R2) ... + LEN(Z2) ) = 0