VBA: Create a named ranged and delete a row from said name range based on value

[IIII]

Hi,

I have this small section that I'm hoping to create a name-range from. In the sheet below, it is the bordered area with red text.

I've been scratching my head trying to use

Cells(Range("A1").End(xlDown).Row + 2, 2)

- which gets me to the first cell in the table, in this case, B19, however, I'm not sure how to do the rest using lines like

Cells(Rows.Count, "A").End(xlUp).Row

as I'm not sure what to use as the guide to find the last row in this situation. Please note that the column lengths of the data above the bordered area are dynamic.

Also, with the bordered area below, is it possible to delete rows for anything that has a value of zero? - I do not mind if this doesn't get solved as the above query is my main problem. Thanks anyway.

Please advise if further information is required. Many thanks in advance for any help with this one. Cheers.

Book1
	A	B	C	D	E	F	G
1	Date	Shift	start time	Finish Time	Hours	Kms	Costs
2	Wed, 06 Apr 2022	Lorem ipsum	8:00 AM	12:00 PM	4	34	0.50
3	Wed, 06 Apr 2022	Lorem ipsum	12:30 PM	3:30 PM	3	22	0.50
4	Thu, 07 Apr 2022	Lorem ipsum	9:00 AM	10:00 AM	1	0	0.00
5	Thu, 07 Apr 2022	TEST SERVICES	10:00 AM	1:00 PM	3	0	0.00
6	Fri, 08 Apr 2022	TEST SERVICES	9:00 AM	12:30 PM	3.5	0	0.00
7	Sat, 09 Apr 2022	Lorem ipsum	6:00 AM	9:30 AM	3.5	168	0.50
8	Sat, 09 Apr 2022	TEST SERVICES	10:00 AM	1:00 PM	3	0	0.50
9	Mon, 11 Apr 2022	Lorem ipsum	9:00 AM	10:00 AM	1	0	0.00
10	Mon, 11 Apr 2022	Lorem ipsum	10:00 AM	4:00 PM	6	199	0.50
11	Tue, 12 Apr 2022	Lorem ipsum	9:00 AM	3:30 PM	6.5	143	0.50
12	Wed, 13 Apr 2022	Lorem ipsum	9:00 AM	12:00 PM	3	131	0.50
13	Wed, 13 Apr 2022	Lorem ipsum	12:30 PM	3:30 PM	3	22	0.50
14	Thu, 14 Apr 2022	Lorem ipsum	9:00 AM	10:00 AM	1	0	0.00
15	Thu, 14 Apr 2022	TEST SERVICES	10:00 AM	1:30 PM	3.5	0	0.00
16	Fri, 15 Apr 2022	Lorem ipsum	9:30 AM	1:30 PM	4	20	0.50
17	Sat, 16 Apr 2022	Lorem ipsum	6:00 AM	9:30 AM	3.5	64	0.50
18
19		Weekday Hours	39
20		Weekday Hours	3.5
21		Weekday Hours	8.5
22		Weekday Hours	1.5
23		Weekday Hours	0
24		Weekday Hours	0
25		Weekday Hours	0
26		Weekday Hours	0
27		Weekday Hours	803
28
Michael Scarn

 

[Alex Blakenburg]

I am not sure if you mean an Excel Named Range or just a VBA variable name. I have assumed the latter for now. See if this gets you started.

Sub GetRange()

    Dim sht As Worksheet
    Dim rngWkDayHrs As Range
    Dim FirstRow As Long, LastRow As Long, i As Long
    
    Set sht = ActiveSheet
    With sht
        FirstRow = .Cells(1, "A").End(xlDown).Row + 2
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rngWkDayHrs = .Range(.Cells(FirstRow, "B"), .Cells(LastRow, "C"))
    
        For i = LastRow To FirstRow Step -1
            If .Cells(i, "C") = 0 Then .Cells(i, "C").EntireRow.Delete
        Next i
    End With

End Sub

 

[IIII]

I am not sure if you mean an Excel Named Range or just a VBA variable name. I have assumed the latter for now. See if this gets you started.

Sub GetRange()

    Dim sht As Worksheet
    Dim rngWkDayHrs As Range
    Dim FirstRow As Long, LastRow As Long, i As Long
   
    Set sht = ActiveSheet
    With sht
        FirstRow = .Cells(1, "A").End(xlDown).Row + 2
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rngWkDayHrs = .Range(.Cells(FirstRow, "B"), .Cells(LastRow, "C"))
   
        For i = LastRow To FirstRow Step -1
            If .Cells(i, "C") = 0 Then .Cells(i, "C").EntireRow.Delete
        Next i
    End With

End Sub

Hi Alex - Thank you for responding.

My apologies for leaving out those details in my initial post. What I actually need is an Excel Named Range for the two columns in the bordered area in my example sheet like:

Range("range" & 'Some range for last row').Name = "Weekday"
Range("range" & 'Some range for last row').Name = "Hours"

I just don't know how to do the above named-range VBA code, for this particular section.

Thanks again for your help.

 

[Alex Blakenburg]

I added creating the names to the previous code:

Sub GetRange()

    Dim wb As Workbook
    Dim sht As Worksheet
    Dim rngWkDayHrs As Range
    Dim FirstRow As Long, LastRow As Long, i As Long
    
    Set wb = ActiveWorkbook
    Set sht = ActiveSheet
    With sht
        FirstRow = .Cells(1, "A").End(xlDown).Row + 2
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rngWkDayHrs = .Range(.Cells(FirstRow, "B"), .Cells(LastRow, "C"))
        
        wb.Names.Add Name:="Weekday", RefersTo:=rngWkDayHrs.Columns(1)
        wb.Names.Add Name:="Hours", RefersTo:=rngWkDayHrs.Columns(2)
    
        For i = LastRow To FirstRow Step -1
            If .Cells(i, "C") = 0 Then .Cells(i, "C").EntireRow.Delete
        Next i
    End With
    
End Sub

 

[IIII]

I added creating the names to the previous code:

Sub GetRange()

    Dim wb As Workbook
    Dim sht As Worksheet
    Dim rngWkDayHrs As Range
    Dim FirstRow As Long, LastRow As Long, i As Long
   
    Set wb = ActiveWorkbook
    Set sht = ActiveSheet
    With sht
        FirstRow = .Cells(1, "A").End(xlDown).Row + 2
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rngWkDayHrs = .Range(.Cells(FirstRow, "B"), .Cells(LastRow, "C"))
       
        wb.Names.Add Name:="Weekday", RefersTo:=rngWkDayHrs.Columns(1)
        wb.Names.Add Name:="Hours", RefersTo:=rngWkDayHrs.Columns(2)
   
        For i = LastRow To FirstRow Step -1
            If .Cells(i, "C") = 0 Then .Cells(i, "C").EntireRow.Delete
        Next i
    End With
   
End Sub

@Alex Blakenburg - Sorry I could've just added that and wasn't paying attention properly...need to sleep. Anyhow, mate, you are a legend!! This works like a charm! Thank you very much for solving both issues for me! Cheers

 

[Alex Blakenburg]

No worries. Glad I could help.