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]

Try this:

Sub copy_cells()
  Dim f As Range
  Dim col As Long, lr As Long, lc As Long
  
  lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  lc = Cells(2, Columns.Count).End(1).Column
  If lr < 4 Then lr = 4
  col = Columns("O").Column
  Set f = Range("2:2").Find("N/A", , xlValues, xlWhole, xlByColumns, xlPrevious, False)
  If Not f Is Nothing Then
    col = f.Column + 1
  End If
  
  Range(Cells(4, col), Cells(lr, lc)).Copy
  
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[smitpau]

Hi Dante,

Thanks for your help that didn't quite work.

I've uploaded an example file in the link below, if you could please use that as the basis for writing the macro so ultimately from the range ("O9:DQ33") only ("Y9:DP33") would be selected to copy.

Example File.xlsx

Microsoft Excel Workbook

1drv.ms

 

[DanteAmor]

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.

According to your sheet, what you wrote is not entirely true.

Then I would need you to explain what the result is that you want, because in your sheet the N/A's are not grouped, they are everywhere.

And by the way, what are you going to copy if the cells are empty?
And are you going to paste it somewhere or do you just need the Copy statement?

 

[smitpau]

Hi Dante,

There are going to be two macro buttons one to copy the actual data ("O9:BI33") and one to copy the forecast data ("BJ9:DQ33").

You're right that it does go (N/A - Actuals - N/A) but for the purposes of the two ranges used by the two macro buttons it will be (N/A - Actuals) and (Actuals - N/A) for the Copy Actuals and Copy Forecast butons respectively.

If the cells are empty then it is fine to still copy and yes I have a separate Paste macro setup so this is just to select the relevant copy range.

The hard part is somehow reducing the range to exclude columns with the Months which have the N/A value in.

Hope I explained that okay, happy to clarify anything.

 

[bebo021999]

May be try:

Option Explicit
Sub copy()
Dim j&, firstNA&, lastNA&, rng
rng = Range("O7:DQ7").Value
For j = 1 To UBound(rng, 2)
    If rng(1, j) <> "N/A" Then
        If firstNA = 0 Then firstNA = j
        lastNA = j
    End If
Next
Range(Cells(9, firstNA + 14), Cells(33, lastNA + 14)).copy
End Sub

 

[smitpau]

Thanks @bebo021999 works like a charm!

Now to spend far too long analysing why it works, haha.

If you could provide a basic explanation of how the code works that would be really appreciated so I can try to write something like this in future.

For instance why is there are 2 in this bit of code UBound(rng, 2)?

 

[smitpau]

May be try:

Option Explicit
Sub copy()
Dim j&, firstNA&, lastNA&, rng
rng = Range("O7:DQ7").Value
For j = 1 To UBound(rng, 2)
    If rng(1, j) <> "N/A" Then
        If firstNA = 0 Then firstNA = j
        lastNA = j
    End If
Next
Range(Cells(9, firstNA + 14), Cells(33, lastNA + 14)).copy
End Sub

How would you edit that code to reference a specific sheet (as the Macro button is not on the same sheet it's being applied to).

Thanks

 

[DanteAmor]

The hard part is somehow reducing the range to exclude columns with the Months which have the N/A value in.

If it was the case where you have N/A anywhere in row 7, then you can use the following macro, it works if the N/A's are anywhere:

Or together:

You can adjust the name of the sheet in the macro.
The macro also considers the last row with data and the last column with data, so you can grow rows or columns without needing to adjust the macro.

Sub justcopy()
  Dim c As Range, rng As Range
  Dim lr As Long
  With Sheets("Sheet1")                                           'fit sheet name
    lr = .Range("O" & Rows.Count).End(3).Row                      'Consider the last row with data
    For Each c In .Range("O7", .Cells(7, Columns.Count).End(1))   'Consider the last column with data
      If c.Value <> "N/A" Then
        If rng Is Nothing Then Set rng = c.Offset(2).Resize(lr - 8) Else Set rng = Union(rng, c.Offset(2).Resize(lr - 8))
      End If
    Next
    If Not rng Is Nothing Then rng.copy
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[smitpau]

Hi Dante,

Perfect that code does exactly what I'm after the Sheet reference is particularly helpful. The auto-sizing of the range is a cool feature as well.

It's fine if you don't have the time but wonder if you could help explain how these do what you say, not obvious to me how either lines of code work.

lr = .Range("O" & Rows.Count).End(3).Row 'Consider the last row with data
For Each c In .Range("O7", .Cells(7, Columns.Count).End(1)) 'Consider the last column with data

Thanks again,

Paul