Help with Range formula to last row.

[Kayslover]

Hi all,

I have the following which is in a macro for a Filter.

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

LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row

I have tried

ws.Range("D4:D&LastRow").AutoFilter Field:=4, Criteria1:=""

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

 

[EXCEL MAX]

Change Field:=4 to Field:=1,

 

[Joe4]

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]

My understanding was that Field 4 is Column D which is for the Filter. What difference should changing the 4 to 1 have?

 

[Akuini]

Try:

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

 

[EXCEL MAX]

Joe4 has explained, and Hakuini how to do that.

 

[Kayslover]

Guys,

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

on the line that says

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

 

[Joe4]

Can you please post your code, in its entirety, so we can see what things like "ws" are assigned to?

 

[Kayslover]

Code as requested:-

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Monthly Cash Expenses for January 2021
2	Ref	Date	From	Treasurer Ref	Receipt Amount	Pay Daven Amount	Pay Pank Amount	Cost Classification			Comments
3	Full Monthly Cash Items Totals				£4,417.00	£690.00	£2,171.00
4	0001	03/01/21	Ancillary Payment	21001	£56.00			Maintenance - Club House			Pay Electrician for plugs in Club House
5	0002	04/01/21	Costco			£500.00		Bar			Bar Purchases
6	0003	03/01/21	Aldi	21002	£3.00			Cleaning Items			For General Cleaning
7	0004	04/01/21	B&M	21003	£4.00			Cleaning Items			For General Cleaning
8	0005	05/01/21	Sainsbury	21004	£5.00			Cleaning Items			For General Cleaning
9	0006	06/01/21	Asda	21005	£6.00			Cleaning Items			For General Cleaning
10	0007	07/01/21	Tesco	21006	£7.00			Cleaning Items			For General Cleaning
11	0008	08/01/21	Morrissions	21007	£8.00			Cleaning Items			For General Cleaning
12	0009	09/01/21	Lidl	21008	£9.00			Cleaning Items			For General Cleaning
13	0010	01/01/21	Poundstretcher	21009	£10.00			Cleaning Items			For General Cleaning
14	0011	02/01/21	Alan Hartley	21010	£11.00			Cleaning Items			For General Cleaning
15	0012	03/01/21	Boss Gas	21011	£12.00			Cleaning Items			For General Cleaning
16	0013	04/01/21	Amazon	21012	£13.00			Cleaning Items			For General Cleaning
17	0014	05/01/21	Canal Cellars	21013	£14.00			Cleaning Items			For General Cleaning
18	0015	06/01/21	Heron Foods	21014	£15.00			Cleaning Items			For General Cleaning
19	0016	07/01/21	Costco	21015	£16.00			Cleaning Items			For General Cleaning
20	0017	08/01/21	Booker	21016	£17.00			Cleaning Items			For General Cleaning
21	0018	09/01/21	Aldi	21017	£18.00			Cleaning Items			For General Cleaning
22	0019	01/01/21	B&M	21018	£19.00			Cleaning Items			For General Cleaning
23	0020	02/01/2021	Sainsbury	21019	£20.00			Cleaning Items			For General Cleaning
24	0021	03/01/2021	Asda	21020	£21.00			Cleaning Items			For General Cleaning
25	0022	04/01/2021	Tesco	21021	£22.00			Cleaning Items			For General Cleaning
26	0023	05/01/2021	Morrissions	21022	£23.00			Cleaning Items			For General Cleaning
27	0024	06/01/2021	Lidl	21023	£24.00			Cleaning Items			For General Cleaning
28	0025	07/01/2021	Poundstretcher	21024	£25.00			Cleaning Items			For General Cleaning
29	0026	08/01/2021	Alan Hartley	21025	£26.00			Cleaning Items			For General Cleaning
30	0027	09/01/2021	Boss Gas	21026	£27.00			Cleaning Items			For General Cleaning
31	0028	01/01/2021	Amazon	21027	£28.00			Cleaning Items			For General Cleaning
32	0029	02/01/2021	Canal Cellars	21028	£29.00			Cleaning Items			For General Cleaning
Monthly Non Cash Debits

Cell Formulas
Range	Formula
A1	A1	="Monthly Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy")
A3	A3	="Full Monthly Cash Items Totals"

Cells with Data Validation
Cell	Allow	Criteria
C4:C24	List	=Brought_Id

 

[Joe4]

You are trying to reference "ws" before you are setting it equal to anything. Here is that excerpt of 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!