Finding lower right cell of a filtered range.

Brian F · Oct 19, 2011

I have a table of data. The table is processed by a filter and is shortened as a result. I need to copy and paste this data range to another worksheet.
The upper left cell is always “M19”.
The right most column is always “CE” but the number of rows always varies.
The bottom right cell may be CE29 or CE35 or CE40.
How can I define the range using vba? How can I tell the Copy/paste routine where that bottom right cell is?
Any help is greatly appreciated.

This is the code from a manual copy.

Range("M19:CE??").Select
Selection.Copy
Application.WindowState = xlMinimized
Application.WindowState = xlNormal

Rick Rothstein · Oct 19, 2011

The following function will return the bottom row number (the last visible row) for an AutoFilter'ed range...

Code:

Function GetFilteredRangeBottomRow() As Long
  Dim HeaderRow As Long, LastFilterRow As Long, Addresses() As String
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    Addresses = Split(.Range((HeaderRow + 1) & ":" & LastFilterRow). _
                      SpecialCells(xlCellTypeVisible).Address, "$")
    GetFilteredRangeBottomRow = Addresses(UBound(Addresses))
  End With
NoFilterOnSheet:
End Function

Brian F · Oct 19, 2011

Thank you so much. I can step through the function and see that it gives me the first ad last row (which is so cool).

I'm enough of a novice that I don't know how to integrate that information with the Copy function however (I'm working hard at learning but there is SO much!).

Could you be kind enough to give me the code that will integrate that information into a Copy routine?

Rick Rothstein · Oct 20, 2011

Part of what you posted originally was this code...

Code:

Range("M19:CE??").Select
Selection.Copy

First, let's see if we can get you out of a bad habit. Very rarely is it necessary to select a range in order to work with it. Whenever you see the above construction, you can replace it with this...

Code:

Range("M19:CE??").Copy

Now, your question is asking how do you substitute a value for the ?? symbols... easy, that is what the GetFilteredRangeBottomRow function returns... a number that you can concatenate into the text at the ?? symbols. This is what it would look like...

Code:

Range("M19:CE" & GetFilteredRangeBottomRow).Copy

mikerickson · Oct 20, 2011

I'd use something like this

Code:

Dim dataRange as Range

dataRange.AdvancedFilter xlFilterInPlace: Rem or AutoFilter

With dataRange.SpecialCells(xlCellTypeVisible)
    With .Areas(.Areas.Count)
        MsgBox .Item(.Cells.Count).Address & " is the last cell that meets the condition."
    End With
End With

Brian F · Oct 20, 2011

I have been going back and removing the "Select" commands from my code but I have noticed that;
 This;
ActiveCell.Range("A1:BB1").Select
Selection.Copy

Can become;
ActiveCell.Range("A1:BB1").Copy

But this;
ActiveCell.Offset(-1, -3).Range("A1:A15").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Can not become;

ActiveCell.Offset(-1, -3).Range("A1:A15").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Without getting; “Runtime error ‘9’: Subscript out of range”

Rick Rothstein · Oct 20, 2011

Brian F said:
I have been going back and removing the "Select" commands from my code but I have noticed that;
This;
ActiveCell.Range("A1:BB1").Select
Selection.Copy

Can become;
ActiveCell.Range("A1:BB1").Copy

But this;
ActiveCell.Offset(-1, -3).Range("A1:A15").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Can not become;

ActiveCell.Offset(-1, -3).Range("A1:A15").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Without getting; “Runtime error ‘9’: Subscript out of range”

Notice the first two words I started my sentence with...

"Very rarely is it necessary to select a range in order to work with it"

Hyperlinks is one of those rare times. You should always try combining the lines and, when it results in an error, undo it and move one. You will not run into this problem very often.

Finding lower right cell of a filtered range.

Brian F

Active Member

Excel Facts

Rick Rothstein

MrExcel MVP

Brian F

Active Member

Rick Rothstein

MrExcel MVP

mikerickson

MrExcel MVP

Brian F

Active Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Finding lower right cell of a filtered range.

Brian F

Active Member

Excel Facts

Rick Rothstein

MrExcel MVP

Brian F

Active Member

Rick Rothstein

MrExcel MVP

mikerickson

MrExcel MVP

Brian F

Active Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock