Selecting visible cells in a table column after manually filtering

PCrawf

New Member
Joined
Aug 23, 2021
Messages
5
Platform
  1. Windows
Hi, I would like help with a macro to select visible cells in specific columns of a table.

My aim is to manually filter my table, then run this macro which will select data from only the visible cells in three columns. Each column data will be copied to another sheet.
I already know how to manually filter my tables, how to write a macro to select various or all cells in columns, even down to the last row, and how to copy, then paste to other sheets. What I do not know, and am struggling with, is how to select only the visible cells of these columns.

I thought it may be selecting the first visible cell below the header, then to the last row, then filtering only visible cells (Alt ; on the keyboard), but i cant find the right code to make it work.

Basically, I only need help to write the code that will select visible cells in a predetermined column.

I do not wish to appear rude, it is just that I have been accused on another forum of not been clear enough. I do appreciate the time people have towards helping others.
Please, no rude or hurtful comments. Thank you.

FYI:
The table header is in row 10 (the columns of concern are E, I & O).
Sheet Name: MedComp
Sheet Number: Sheet30
Table Name: TBL_MedComp
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,758
Office Version
  1. 2016
Platform
  1. Windows
The command is something like this
VBA Code:
Sheets("Sheet1").Range("B2:B200").SpecialCells(xlCellTypeVisible).Select
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
153
Office Version
  1. 365
Platform
  1. Windows
Once the column has been filtered this should work to select visible cells

VBA Code:
Selection.CurrentRegion.Select
Selection.Copy

This is the equivalent to Ctrl+* command in Excel. You can then paste it where ever you need it.
 

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
133
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
Sub copyData()
    Dim rng As Range
    With Sheets("Med Comp")
        If .AutoFilterMode = True Then
            Set rng = .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)
            Set rng = Intersect(rng, .Range("E:E,I:I,O:O"))
            rng.Copy Sheets("Sheet2").Range("A1")
        End If
    End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option
VBA Code:
Sub PCrawf()
   With Sheets("MedComp").ListObjects("TBL_MedComp")
      Intersect(.DataBodyRange.SpecialCells(xlVisible), .Parent.Range("E:E,I:I,O:O")).Select
   End With
End Sub
Although there is very rarely any need to select anything.
 
Solution

PCrawf

New Member
Joined
Aug 23, 2021
Messages
5
Platform
  1. Windows
The command is something like this
VBA Code:
Sheets("Sheet1").Range("B2:B200").SpecialCells(xlCellTypeVisible).Select
Thank you but this will select all visible cells in the entire column including table header and data outside the table. I would like to focus on only data in the table. I appreciate your help.
 

PCrawf

New Member
Joined
Aug 23, 2021
Messages
5
Platform
  1. Windows

ADVERTISEMENT

Once the column has been filtered this should work to select visible cells

VBA Code:
Selection.CurrentRegion.Select
Selection.Copy

This is the equivalent to Ctrl+* command in Excel. You can then paste it where ever you need it.
Thank you but I believe this implies I have manually selected the data already. I am trying to generate a macro to handle the manual task of selecting the data for me as it will be applied to multiple columns. Thank you for your input.
 

PCrawf

New Member
Joined
Aug 23, 2021
Messages
5
Platform
  1. Windows
VBA Code:
Sub copyData()
    Dim rng As Range
    With Sheets("Med Comp")
        If .AutoFilterMode = True Then
            Set rng = .AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)
            Set rng = Intersect(rng, .Range("E:E,I:I,O:O"))
            rng.Copy Sheets("Sheet2").Range("A1")
        End If
    End With
End Sub
Thank you, but this code didn't select or copy the data to the destination sheet. I'm not sure how to troubleshoot it as it did not give an error either. Thank you though. The post by Fluff has worked though.
 

PCrawf

New Member
Joined
Aug 23, 2021
Messages
5
Platform
  1. Windows
Another option
VBA Code:
Sub PCrawf()
   With Sheets("MedComp").ListObjects("TBL_MedComp")
      Intersect(.DataBodyRange.SpecialCells(xlVisible), .Parent.Range("E:E,I:I,O:O")).Select
   End With
End Sub
Although there is very rarely any need to select anything.
Fluff, thank you this worked as I wanted. My next step is to adapt this and rather than leaving as a Selection, I'll copy it to my destination. I'll play with a few ways to do this as I am still learning the various ways. I appreciate your time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,969
Members
422,252
Latest member
wannabegeek1

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
Top