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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
929
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am requiring help with the below formula:

VBA Code:
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)

1592862163120.png


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:

VBA Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You can use this, and the Log sheet does not need to be the active sheet at the time of execution.

VBA Code:
With Sheets("Log")
.Range(.Cells(2, 10), .Cells(.Cells(.Rows.Count, 10).End(xlUp).Row, 10)).SpecialCells(12).Copy
End With
 
Upvote 0
Check if the following works for you

VBA Code:
  Sheets("Log").Select
  Range("J1", Range("J" & Rows.Count).End(3)).Offset(1).Copy
 
Upvote 0
You can use this, and the Log sheet does not need to be the active sheet at the time of execution.

VBA Code:
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?
 
Upvote 0
Check if the following works for you

VBA Code:
  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 :)
 
Upvote 0
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."
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top