Help! Select blank cell in a filtered list

Another approach.

Why not make the total above the selection (e.g. in cell A2).

The advance of that approach is that you alway see, the result (and it can also be printed on all sheets).

Another approach.

Make a pivot table of your data.

If you don't want to use PV Tables, you could try something like:

dim rngClient, rngAmount as Range
dim n as integer

set rngClient = Activeworkbook.Activeworksheet.Range("A:A")
set rngAmount = Activeworkbook.Activeworksheet.Range("B:B")

n = Activeworkbook.Activeworksheet.UsedRange.Rows().Count + 3

rngClient.Rows(n).Value = "Total"
rngAmount.Rows(n).Value = [Your filtered sum]

This should get you to where you can start entering data 3 rows after the used range.

*changed to usedrange.rows() for n

Hi yoko21

Play with this

Option Explicit

Sub SubTot()

    Dim LR As Long, LR1 As Long
    With Sheets("Sheet1")
        
        'Find Last Row in Column A With Data (plus 1)
        LR1 = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        'Find True Last Row With Data (plus 3)
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row + 3
        
        'Clear Data from Column B below LR1 (the Old Formula)
        .Range("B" & LR1 & ":B" & LR).ClearContents
        
        'Find the New True Last Row (plus 3)
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row + 3
        
        'Put this Formula in that Cell
        .Range("B" & LR).Formula = "=SUBTOTAL(109,B2:B" & LR - 3 & ")"
        
    End With
End Sub

Originally Posted by jaslake

Hi yoko21

Play with this

Option Explicit

Sub SubTot()

    Dim LR As Long, LR1 As Long
    With Sheets("Sheet1")
        
        'Find Last Row in Column A With Data (plus 1)
        LR1 = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        'Find True Last Row With Data (plus 3)
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row + 3
        
        'Clear Data from Column B below LR1 (the Old Formula)
        .Range("B" & LR1 & ":B" & LR).ClearContents
        
        'Find the New True Last Row (plus 3)
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row + 3
        
        'Put this Formula in that Cell
        .Range("B" & LR).Formula = "=SUBTOTAL(109,B2:B" & LR - 3 & ")"
        
    End With
End Sub

Hi John,

I tested your code in a 20 client list and noticed the following:
Without filtering - It worked perfectly. The computation was made 3 cells below the last client in the list.
Filtered. Only including the clients from 1-10 and 15-20. - The computation was made 2 cells below the last client in the list.
Filtered. Only including clients from 1-15. No computation was made and deleted the last to data in column B.

Hi yoko21

Please don't Quote entire Posts...include only those items that are relevant.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.