VBA Selecting visible cells in one column based on the last row of another column

[willow1985]

Hello,

I am requiring help with the below formula:

Sheets("Log").Select
    Columns(10).Cells.SpecialCells(xlCellTypeVisible).Cells(2).Select
    Range(Selection, Selection.End(xlDown)).Select
   Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

After data is filtered this code is run and it will select the first cell after the headers and then select every cell that is visible below and copy the data. The problem is Column 10 (J) may have blank cells in between the last cell with data in that column (see picture)

In this section of the code: Range(Selection, Selection.End(xlDown)).Select - is there a way to modify this that instead of going to Selection.End based on column 10 that it does does it based on the last row of data visible in column 1(A)?

I tried something like this but I am missing something:

Sheets("Log").Select
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
    Columns(10).Cells.SpecialCells(xlCellTypeVisible).Cells(2).Select
    Range(Selection, LastRowColumnA).Select
   Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

Thank you to anyone who could help

Carla

 

[Tom Urtis]

You can use this, and the Log sheet does not need to be the active sheet at the time of execution.

With Sheets("Log")
.Range(.Cells(2, 10), .Cells(.Cells(.Rows.Count, 10).End(xlUp).Row, 10)).SpecialCells(12).Copy
End With

 

[DanteAmor]

Check if the following works for you

  Sheets("Log").Select
  Range("J1", Range("J" & Rows.Count).End(3)).Offset(1).Copy

 

[willow1985]

You can use this, and the Log sheet does not need to be the active sheet at the time of execution.

With Sheets("Log")
.Range(.Cells(2, 10), .Cells(.Cells(.Rows.Count, 10).End(xlUp).Row, 10)).SpecialCells(12).Copy
End With

Works perfectly! Thank you.

I am assuming the SpecialCells(12) is the part that makes sure all of the data is selected for column 10 by going to the last row of data in all 12 columns correct?

 

[willow1985]

Check if the following works for you

  Sheets("Log").Select
  Range("J1", Range("J" & Rows.Count).End(3)).Offset(1).Copy

Thank you as always DanteAmor, I went with Tom's code this time but both were equally efficient.

Thank you again

 

[Tom Urtis]

I am assuming the SpecialCells(12) is the part that makes sure all of the data is selected for column 10 by going to the last row of data in all 12 columns correct?

Not exactly correct. The preceding portion of that code line only (as it should) reference column 10 which is column J because that is the column being copied for its visible cells. And that is what the SpecialCells(12) specifically refers to: visible cells in column J (you already handled the filter part of the macro). In layperson's terms, the code line
.Range(.Cells(2, 10), .Cells(.Cells(.Rows.Count, 10).End(xlUp).Row, 10)).SpecialCells(12).Copy
can be interpreted as
"Copy only the visible cells on the Log sheet that are in the range of J2 to the cell holding the last value in column J."