Help with Range formula to last row.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Hi all,

I have the following which is in a macro for a Filter.
VBA Code:
ws.Range("D4:D1000 ").AutoFilter Field:=4, Criteria1:=""
Is it possible to change the D1000, in the formula to point to the last row in Column D?

Just before the ws.Range I have
VBA Code:
LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row

I have tried
VBA Code:
ws.Range("D4:D&LastRow").AutoFilter Field:=4, Criteria1:=""
and I get a “Runtime error 1004, Method ‘Range’ of object_Worksheet failed.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Change Field:=4 to Field:=1,
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,749
Office Version
  1. 365
Platform
  1. Windows
Chnage Field:=4 to Field:=1,
Just to follw up on this. Note that the field number is relative to the size of your range (as opposed to the whole worksheet).
Since your range is only one column, you Field number can only be 1.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
My understanding was that Field 4 is Column D which is for the Filter. What difference should changing the 4 to 1 have?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try:
VBA Code:
ws.Range("D4:D" & LastRow).AutoFilter Field:=1, Criteria1:=""
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Joe4 has explained, and Hakuini how to do that.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Guys,

I get a Runtime error 1004, Method ‘Range’ of object_Worksheet failed.

on the line that says
VBA Code:
ws.Range("D4:D" & LastRow).AutoFilter Field:=1, Criteria1:=""
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,749
Office Version
  1. 365
Platform
  1. Windows
Can you please post your code, in its entirety, so we can see what things like "ws" are assigned to?
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Code as requested:-

VBA Code:
Option Explicit
Public Sub ConsolidateExpenses()

Application.ScreenUpdating = False
Application.DisplayAlerts = True
Application.StatusBar = True
    
Dim a()
Dim sht
Dim ws As Worksheet
Dim rf As Range
Dim i As Integer
Dim d As Long
Dim MyNoOfWeek As Integer
Dim LstRw As Long, PrnG As Range
Dim LastRow As Long
'Dim WshSrc As Worksheet
'Dim WshTrg As Worksheet
'Set WshSrc = ThisWorkbook.Worksheets("Monthly Cash Debits")
'Set WshTrg = ThisWorkbook.Worksheets("Monthly Non Cash Debits")
        
Application.DisplayAlerts = False
        
'Get the number of weeks in the month from sheet Formula, cell H2
    
    Sheets("Formula").Select
        MyNoOfWeek = Range("H2").Value
            
'Have to unprotect the Weekly Sheets and Formula sheets.

'The weekly sheets are being referenced here by their Excel Internal names as the sheet names change every month, were as the Internal names stay the same.
    
    Sheet1.Unprotect Password:=""
    Sheet8.Unprotect Password:=""
    Sheet10.Unprotect Password:=""
    Sheet11.Unprotect Password:=""
    Sheets("Monthly Cash Debits").Unprotect Password:=""
    Sheets("Monthly Non Cash Debits").Unprotect Password:=""
    Sheets("Formula").Unprotect Password:=""
    
    If MyNoOfWeek = 5 Then
       Sheet12.Unprotect Password:=""
   End If

'If the number of week in the month is 4, then set the array to 4 sheets, otherwise set it to 5 sheets.
'The array has to be build to extract the required data from either 4 or 5 weekly sheets that the month has.

    If MyNoOfWeek = 4 Then
        sht = Array(Sheet1, Sheet8, Sheet10, Sheet11)
    Else
        sht = Array(Sheet1, Sheet8, Sheet10, Sheet11, Sheet12)
    End If

Sheets("Monthly Cash Debits").Select
    Range("A1").Select

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Sheets("Monthly Cash Debits").UsedRange.Offset(3).ClearContents
    For i = 0 To UBound(sht)
        With sht(i)
            Set rf = .Columns.Find("Ref")
            If Not rf Is Nothing Then
                Set rf = rf.Offset(1).Resize(.Columns(1).Find("B", LookAt:=xlWhole).Row - rf.Row - 1, 14)       'This statement extracts from Col A to Col N
                If Not rf Is Nothing Then
                    On Error Resume Next
                    a = rf.Columns(1).SpecialCells(xlCellTypeConstants).Resize(, 14).Value                           'This statement extracts from Col A to Col N
                    If Err.Number = 0 Then
                        With Sheets("Monthly Cash Debits")
                            With .Range("A" & .Cells(Rows.Count, "A").End(3).Row)(2)
                                .Resize(UBound(a), 14) = a                                                                          'This statement extracts from Col A to Col N
                                Erase a
                            End With
                        End With
                    End If
                    Err.Clear
                End If
            End If
        End With
    Next

'Change the value of the sum in columns E, F and G to paste Values.

    With Sheets("Monthly Cash Debits")
        i = .[a3].CurrentRegion.Columns(1).Rows.Count - 3
        With .[e3].Resize(, 3)
            .FormulaR1C1 = "=sum(r[1]c:r[" & i & "]c)"
            .Value = .Value
        End With
    End With
Set rf = Nothing

'Set Columns Widths.

    Sheets("Monthly Cash Debits").Select
        Columns("A").ColumnWidth = 7
        Columns("B").ColumnWidth = 10
        Columns("C").ColumnWidth = 21
        Columns("D:G").ColumnWidth = 16
        Columns("H:J").ColumnWidth = 12
        Rows("3:200").RowHeight = 30
        
'Copy sheet Monthly Cash Debits and rename it Monthly Non Cash Debits

ActiveSheet.Name = "Monthly Non Cash Debits"

Application.DisplayAlerts = False

' Last statement put in to suppress the dialog box that asks the user to confirm the sheet deletion
    
    Sheets("Monthly Non Cash Debits").Delete
    ActiveSheet.Name = "Monthly Cash Debits"
    Sheets("Monthly Cash Debits").Copy after:=Sheets("Monthly Cash Debits")
    ActiveSheet.Name = "Monthly Non Cash Debits"
    
'Last statement renames the new sheet which will have been called Monthly Cash Debits (2) to Monthly Non Cash Debits

'Delete unrequired rows from sheets Monthly Cash Debits and Monthly Non Cash Debits

'Monthly Cash Debits should only shows items that have a Treasurer Ref in Column D (i.e. NON Blank).

'Monthly Non Cash Debits should only shows items that DO NOT have a Treasurer Ref in Column D (i.e. Blank).

'It is acheived by setting a filter in both sheets and using "Blanks" and "Non Blanks" in Column D to selete what should be kept and what should be deleted.
  
'Set reference to the sheet in the workbook.

'*******************************************************************************************************************************************************************************************************************************
'* Code from below here is not working                                                                                                                                                                                                                             * 
'*******************************************************************************************************************************************************************************************************************************                       
'Work the filter on sheet called Monthly Cash Debits
'The following works out what the last row that contains data in Column A

    'ActiveSheet.Name = "Monthly Cash Debits"
    'With ActiveSheet
        'LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'End With
    
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    Set ws = ThisWorkbook.Worksheets("Monthly Cash Debits")
    ws.Activate 'not required but allows user to view sheet if warning message appears
  
'Clear any existing filters
      On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0

'1. Apply Filter to look for BLANKS in Column D in the Criteria1 and uses the Last row to set the range.

'ws.Range ("D4:D1000 ").AutoFilter Field:=4, Criteria1:=""
      
    ws.Range("D4:D" & LastRow).AutoFilter Field:=1, Criteria1:=""
    'ws.Range("D4:D1000 ").AutoFilter Field:=4, Criteria1:=""
  
'2. Delete Rows
      Application.DisplayAlerts = False
'ws.Range("D4:D1000").SpecialCells(xlCellTypeVisible).Delete
        ws.Range("D4:D" & LastRow).SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
  
'3. Clear Filter
      On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0
  
'Work the filter on sheet called Monthly Non Cash Debits
'LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'The following works out what the last row that contains data in Column A

      'Sheets("Monthly Non Cash Debits").Unprotect
      'With ActiveSheet
        'LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'End With
  
'Set reference to the sheet in the workbook.
    Set ws = ThisWorkbook.Worksheets("Monthly Non Cash Debits")
    ws.Activate 'not required but allows user to view sheet if warning message appears
  
    'Clear any existing filters
    On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0
    
'1. Apply Filter to look for NON BLANKS in Column D by using the <> in the Criteria1

'ws.Range("D4:D1000").AutoFilter Field:=4, Criteria1:="<>"
    ws.Range("D4:D" & LastRow).AutoFilter Field:=1, Criteria1:="<>"
      
  
'2. Delete Rows
    Application.DisplayAlerts = False
        ws.Range("D4:D" & LastRow).SpecialCells(xlCellTypeVisible).Delete
'ws.Range("D4:D1000").SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
  
'3. Clear Filter
    On Error Resume Next
        ws.ShowAllData
    On Error GoTo 0

    Sheets("Monthly Non Cash Debits").Select
        Columns("C").ColumnWidth = 43
        Range("A3").Value = "Full Monthly Non Cash Items Totals"
        
'The above sets the title in A3 for sheet Monthly Non Cash Debits

'Hide columns in sheets Monthly Cash Debits and Monthly Non Cash Debits so that you only see data that is relevant.

    Sheets("Monthly Cash Debits").Select
    Columns("F:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Monthly Non Cash Debits").Select
    ActiveSheet.Unprotect
    Columns("D:E").Select
    Selection.EntireColumn.Hidden = True

'Set Print area for sheet
    
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row
    Set PrnG = Range("A1:M" & LstRw)    ' or whatever column you want
    ActiveSheet.PageSetup.PrintArea = PrnG.Address
        
'Password protect all Sheets in the workbook, But allow formatting cells (so that when you select a cell the colour changes) and to allow Inserting of rows.

    For Each ws In ActiveWorkbook.Worksheets
            ws.Protect Password:="", AllowFormattingCells:=True, AllowInsertingRows:=True, AllowFiltering:=True
     Next ws

'Sheets named Monthly Totals, Monthly Receipt No, Monthly Cash Debits, Monthly Non Cash Debits, and Lookup should not be Protected.

    'Sheets("Monthly Totals").Unprotect ""
    'Sheets("Monthly Receipt No").Unprotect ""
    Sheets("Lookup").Unprotect ""
    Sheets("Monthly Cash Debits").Unprotect ""
    Sheets("Monthly Non Cash Debits").Unprotect ""
    
    Sheets("Monthly Cash Debits").Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
    MsgBox "Consolidatation of monthly data has completed.", Title:="Bolton Indians Sports Club - Monthly Data Consolidation"
    
End Sub

Below is the sample data

01 January.xlsm
ABCDEFGHIJKLM
1Monthly Cash Expenses for January 2021
2RefDateFromTreasurer RefReceipt AmountPay Daven AmountPay Pank AmountCost ClassificationComments
3Full Monthly Cash Items Totals£4,417.00£690.00£2,171.00
4000103/01/21Ancillary Payment21001£56.00Maintenance - Club HousePay Electrician for plugs in Club House
5000204/01/21Costco£500.00BarBar Purchases
6000303/01/21Aldi21002£3.00Cleaning ItemsFor General Cleaning
7000404/01/21B&M21003£4.00Cleaning ItemsFor General Cleaning
8000505/01/21Sainsbury21004£5.00Cleaning ItemsFor General Cleaning
9000606/01/21Asda21005£6.00Cleaning ItemsFor General Cleaning
10000707/01/21Tesco21006£7.00Cleaning ItemsFor General Cleaning
11000808/01/21Morrissions21007£8.00Cleaning ItemsFor General Cleaning
12000909/01/21Lidl21008£9.00Cleaning ItemsFor General Cleaning
13001001/01/21Poundstretcher21009£10.00Cleaning ItemsFor General Cleaning
14001102/01/21Alan Hartley21010£11.00Cleaning ItemsFor General Cleaning
15001203/01/21Boss Gas21011£12.00Cleaning ItemsFor General Cleaning
16001304/01/21Amazon21012£13.00Cleaning ItemsFor General Cleaning
17001405/01/21Canal Cellars21013£14.00Cleaning ItemsFor General Cleaning
18001506/01/21Heron Foods21014£15.00Cleaning ItemsFor General Cleaning
19001607/01/21Costco21015£16.00Cleaning ItemsFor General Cleaning
20001708/01/21Booker21016£17.00Cleaning ItemsFor General Cleaning
21001809/01/21Aldi21017£18.00Cleaning ItemsFor General Cleaning
22001901/01/21B&M21018£19.00Cleaning ItemsFor General Cleaning
23002002/01/2021Sainsbury21019£20.00Cleaning ItemsFor General Cleaning
24002103/01/2021Asda21020£21.00Cleaning ItemsFor General Cleaning
25002204/01/2021Tesco21021£22.00Cleaning ItemsFor General Cleaning
26002305/01/2021Morrissions21022£23.00Cleaning ItemsFor General Cleaning
27002406/01/2021Lidl21023£24.00Cleaning ItemsFor General Cleaning
28002507/01/2021Poundstretcher21024£25.00Cleaning ItemsFor General Cleaning
29002608/01/2021Alan Hartley21025£26.00Cleaning ItemsFor General Cleaning
30002709/01/2021Boss Gas21026£27.00Cleaning ItemsFor General Cleaning
31002801/01/2021Amazon21027£28.00Cleaning ItemsFor General Cleaning
32002902/01/2021Canal Cellars21028£29.00Cleaning ItemsFor General Cleaning
Monthly Non Cash Debits
Cell Formulas
RangeFormula
A1A1="Monthly Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy")
A3A3="Full Monthly Cash Items Totals"
Cells with Data Validation
CellAllowCriteria
C4:C24List=Brought_Id
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,749
Office Version
  1. 365
Platform
  1. Windows
You are trying to reference "ws" before you are setting it equal to anything. Here is that excerpt of code:
VBA Code:
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    Set ws = ThisWorkbook.Worksheets("Monthly Cash Debits")
You need to flip those two lines around, as you cannot reference "ws" before you set it equal to anything!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,163
Messages
5,629,060
Members
416,363
Latest member
zaveedd

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