Macro to Copy Range using Criteria

[smitpau]

Hi,

I'm trying to write a macro for a range to only copy the values in the columns which do not have N/A in the 2nd row.

So for instance using this example below columns O:X would be excluded because they have N/A in row 2 but columns Y:AB would be included, so the Macro should select say ("Y4:AB50") to copy from the total input range of ("O4:AB50").

For reference the N/A's are all grouped together so after the last N/A all the other columns within the range can be used, the amount of N/A's can change.

I imagine it would something like a Macro if statement but ideally do not want to have to write the IF statement for each column to be selected.

Happy to expand if that is not explained very clearly.

Thanks,

Paul

 

[DanteAmor]

lr = .Range("O" & Rows.Count).End(3).Row

it takes column "O" as a reference, of the total number of rows in the sheet (rows.count) it goes up (End(3) or End(xlUp) is the same) and where the cursor stops it takes the row (Row ). That is the last row with data from column "O".​

.Cells(7, Columns.Count).End(1))

it takes row 7 as a reference, of the total number of columns in the sheet (Columns.count) it goes to the left (End(1) or End(xlToLeft) is the same) and where the cursor stops it takes the row (Row). That is the last column with data from row "7".​

The number 3 is the numeric value for the xlUp parameter:

​

You can use xlUp or 3:​

​

xlDown = 4​

xlToLeft = 1​

xlToRight = 2​

xlUp = 3​

I hope it helps you.