The correct row is not being highlighted and having appropriate code run on that row

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is a strange error as it happens to me sometimes, but not every time. There are two procedures that both are used to identify a row and do something to it. They are called Transfer and LateCancel. They both are meant to cycle through each row, highlighting one row at a time and ask "is this the row?" When the user selects Yes it does something to the row and exits the sub. If No is selected, the highlighting is removed from that row and the next row is highlighted, asking the same question. The subs are initiated from a totals sheet where there 2 spots to enter a date and a request number in some cells. One spot for the Transfer sub to run and one spot for the LateCancel.



The transfer sub is not self explanatory from it's name. This is run on jobs that are to be cancelled.

Order of events
  1. I run the sub Transfer from the totals sheet through a command button
  2. Row 4 in the July sheet is highlighted
  3. I am asked the question, "Is this the job you want to cancel?"
  4. I press No and the highlighting moves to row 5 and asks the same question.
  5. I press Yes on this row and the row is moved to the cancellations sheet and the sub is exited
  6. I run the sub again from the totals sheet
  7. Row 4 in the July sheet is highlighted
  8. I am asked the question, "Is this the job you want to cancel?"
  9. I press No and the highlighting moves to row 5 and asks the same question.
  10. I press Yes on this row and the row is moved to the cancellations sheet and the sub is exited
    1. This is exactly what I want it to do so far
  11. I now go back to the totals sheet and run the LateCancel sub
  12. There is one row left in the July sheet that matches the filters of the autofilter so I would expect that row to be highlighted
  13. The header row is highlighted next, where it should have highlighted the remaining row that matches the autofilter. It also asks me the above question.
  14. If I keep pressing No, the row is highlighted agan after each time and asked the same question
  15. If I press Yes, I get the error message that "No jobs have been selected as a late cancel"
  16. I run the LateCancel sub again from the totals sheet and it repeats the steps from step 12

  1. I know that this is a sometimes error as if I re-open my file and run the LateCancel sub first, the 3 jobs are cycled though as expected, and I am asked the question each time.
  2. If I press Yes or No, it does what it's meant to do




I have been doing some testing and this is the totals sheet, where the information is entered. I leave it the same each time and just run the subs.

CSS Work Allocation Sheet.77.xlsm
BCDEFG
22Cancelling a job
23To cancel a job, enter the request number in B25 followed by the date of the proposed job in B27 then press enter and select the cancel the Cancel a job button.
24Request number
2595
26Date
275/07/2021
28
29Late Cancel
30To enter a job as a late cancel, enter a request number first in B32, followed by the date of the proposed job in B37 then press enter and select the Add Late Cancel button.
31Request number
3295
33Everytime you leave this sheet and return, this will be 3
34Hours charged for a late cancel
353
36Date
375/07/2021
Totals
Cells with Data Validation
CellAllowCriteria
B27Any value
B25Any value


I have been testing on the one date 5/07/2021 and here is a monthly sheet in the same document as the above sheet where the data is held.
CSS Work Allocation Sheet.77.xlsm
ABCDEFGHIJ
1501 CSS July
2
3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GST
45/07/202195Tutoring$2.00
55/07/202195asdfSupervised Contact$219.30$21.93$241.23
65/07/202195asdfSupervised contact$219.30$21.93$241.23
July
Cell Formulas
RangeFormula
I5I5=IF(E5="Activities",0,H5*0.1)
J5J5=I5+H5
Cells with Data Validation
CellAllowCriteria
F6:J6Any value



This is my Transfer sub
VBA Code:
Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, AutoFilterCounter As Long
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        Dim SheetCounter As Integer: SheetCounter = 0
'Call TurnOffFunctionality
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt           ' autofilter for the value in cell [B27]
                                .AutoFilter 3, Req          ' autofilter for the value in cell [B25]
                                                                    'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                    If ws.[A3].Cells.Offset(1, 0) = "" Then
                                        .AutoFilter
                                        SheetCounter = SheetCounter + 1
                                        'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                        If SheetCounter = 12 Then
                                            MsgBox "A job with the date and request number entered does not exist"
                                        End If
                                        GoTo SkipSheet
                                    End If
                                    
                                'Check the count Autofilter
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                'If value less than 2, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then
                                    .AutoFilter
                                    'Add 1 to a sheet counter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipSheet
                                End If
                                
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer, n As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    n = 0
                                    For Each RowLine In rng.SpecialCells(xlCellTypeVisible)
                                        ws.Activate
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'Copy and paste the selected row
                                            RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                            RowLine.EntireRow.Delete
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            'RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        If answer = vbNo Then n = n + 1
                                    Next RowLine
                                If n > 0 Then MsgBox " You have chosen not to cancel any of the " & n & " job/s !!"
                                End If
                                
FoundRightJob:

                                .AutoFilter                 ' turn off the autofilter
                        End With
                End If
SkipSheet:
        Next ws


'sh.Range("B25,B27").ClearContents
'Call TurnOnFunctionality
End Sub


This is my LateCancel sub
VBA Code:
Sub LateCancel()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Dim Serv As String, Month As String, Service As String, LCPrice As String, AutoFilterCounter As Long
        Set wb2 = ThisWorkbook
        'QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")

        'values on totals sheet that the user is looking for
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        'Dim LCDt As String: LCDt = sh.Cells(37, 2).Value
        Dim LCDt As String: LCDt = CDate(sh.Cells(37, 2).Value)
        Dim LateCancelHours As String: LateCancelHours = sh.Cells(35, 2).Value
        Dim SheetCounter As Long: SheetCounter = 0
        
        WbPath = ThisWorkbook.Path
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
'Call TurnOffFunctionality
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        'If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
    
        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                'On Error Resume Next
                                'Autofilter the late cancel date enter in B37 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                
                                'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                If ws.[A3].Cells.Offset(1, 0) = "" Then
                                    .AutoFilter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipNextSheet
                                End If
                                
                                'Check the count Autofilter
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                'If value less than 2, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then
                                    .AutoFilter
                                    'Add 1 to a sheet counter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipNextSheet
                                End If
                                
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        'Check if there is a service entered in column 5 of the filtered job.
                                        
                                        If .Areas(1).Cells(1, 5).Value = "" Then
                                            'Display a messagebox with a message and the sheet that has the missing service.
                                            MsgBox "There is a job in the " & ws.Name & " sheet that matches the date and request number but does not have a " & _
                                            "service type. Please add a service type to this job before continuing."
                                            Call TurnOnFunctionality
                                            .AutoFilter
                                            'Cells(32, 2).ClearContents
                                            'Cells(37, 2).ClearContents
                                            Exit Sub
                                        End If
                                        'If the service column, (5), has a value, store the service in the service variable.
                                        Service = .Areas(1).Cells(1, 5).Value
                                    End With
                                
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    For Each RowLine In rng.SpecialCells(xlCellTypeVisible)
                                        ws.Activate
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        'If answer = vbNo
                                        
                                    Next RowLine
                                End If
FoundRightJob:
                                'No has been selected each time so display a message box and exit sub
                                If RowNumber < 1 Then
                                    MsgBox "No jobs have been selected as a Late Cancel"
                                    .AutoFilter
                                    Exit Sub
                                End If
                                    'Copy data fom the job back to a calculator on the data sheet (this is the code name for sheet2) to calulcate the price again.
                                    With Data
                                        .Cells(30, 1) = CDate(LCDt)
                                        '.Cells(30, 1) = Format(Date, "d/mm/yyyy")
                                        '.Cells(30, 1).NumberFormat = "d/mm/yyyy"
                                        .Cells(30, 2) = Service
                                        'Set the hourly figure in the lateCanel table to be the LateCancelHours variable
                                        .Cells(30, 5) = LateCancelHours
                                        'A late cancel will be charged for 1 staff member attending
                                        'Therefore, set the Staff Req. figure to 1
                                        .Cells(30, 6) = 1
                                    End With
                                    On Error GoTo Price
                                        'Calculates price of late cancel on worksheet so the new price will be copied to the allocation sheet instead of the previous price
                                        Calculate
                                    LCPrice = Data.Cells(30, 8).Value
Price:
                                Select Case Err.Number
                                    Case Is = 13
                                        MsgBox "There is a problem with the spelling of the service type on the " & ws.Name & " sheet for the job that matches " _
                                        & "the date and request number. Please check the spelling and try again."
                                        'Cells(32, 2).ClearContents
                                        'Cells(37, 2).ClearContents
                                        .AutoFilter
                                        Call TurnOnFunctionality
                                        Exit Sub
                                End Select
                                On Error GoTo 0
                                With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                    Dim LTCnclDate As String
                                    .Areas(1).Cells(RowNumber, 1).Value = "LT CNCL " & .Areas(1).Cells(1, 1).Value
                                    .Areas(1).Cells(RowNumber, 8).Value = LCPrice
                                    .Areas(1).Cells(RowNumber, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                    .Areas(1).Cells(RowNumber, 10).Formula = "=RC[-1]+RC[-2]"
                                End With
                                .AutoFilter
                        End With
                End If
SkipNextSheet:
        Next ws
'sh.Range("B32,B37").ClearContents
'Call TurnOnFunctionality
End Sub

Can someone help me work out why this occurs sometimes and not other times please?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can someone help me with this please? I am still relatively new to vba and can't work out myself.

I understand it may seem like a lot of information or a lengthy question. I have asked questions before and have been told I need to include all the information with the initial question. Maybe some of this information is needed for the question, maybe not. I included it just to be sure.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The only thing I can see is that your last row variable in both marcros is based on column A. Then the rng variable is set by using that LastRow variable . If, after deleting other rows from the filter, there are no values in column A below row 3 of column A with the filter on, then it would keep identifying the first visible row with the highlight. But that is just guessing. It is my bed time, I have failing eyesight and I could be wrong.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for getting back to me.

I have kept testing and this is my data

CSS Work Allocation Sheet.77.xlsm
ABCDEFGHIJ
1501 CSS July
2
3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GST
45/07/202195oneSupervised contact$219.30$21.93$241.23
55/07/202195twoSupervised Contact$219.30$21.93$241.23
65/07/202195threeSupervised Contact$219.30$21.93$241.23
July
Cell Formulas
RangeFormula
I4I4=IF(E4="Activities",0,H4*0.1)
J4J4=I4+H4
Cells with Data Validation
CellAllowCriteria
F4:J4Any value


This is what happened.
  1. I ran the testing sub twice and selected Yes the first time I was asked if this is the row. Both times the row was moved to the cancellations sheet as expected.
  2. I then ran it for a third time and it didn't highlight the last row that was left like it should have but highlighted the header row (row 1)
  3. It doesn't appear therefore to be localised to the sequence of event I gave you earlier.
I tried to do the same thing with the LateCancel feature
  1. I ran the LateCancel sub once.
  2. The first line was highlighted and I was asked the question, to which I hit Yes
  3. The first line (row 4) had pricing and name change applied to it
  4. My July sheet now looked like this
  5. CSS Work Allocation Sheet.77.xlsm
    ABCDEFGHIJ
    1501 CSS July
    2
    3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GST
    4LT CNCL 5/07/202195oneSupervised contact$219.30$21.93$241.23
    55/07/202195twoSupervised Contact$219.30$21.93$241.23
    65/07/202195threeSupervised Contact$219.30$21.93$241.23
    July
    Cell Formulas
    RangeFormula
    I4I4=IF(E4="Activities",0,H4*0.1)
    J4J4=I4+H4
    Cells with Data Validation
    CellAllowCriteria
    F4:J4Any value
  6. I ran LateCancel again
  7. The second line (row 5) was highlighted and I pressed Yes to the question.
  8. The third line had the changed applied to it, where it should have been the second.
  9. My July sheet looked like this
  10. CSS Work Allocation Sheet.77.xlsm
    ABCDEFGHIJ
    1501 CSS July
    2
    3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GST
    4LT CNCL 5/07/202195oneSupervised contact$219.30$21.93$241.23
    55/07/202195twoSupervised Contact$219.30$21.93$241.23
    6LT CNCL 5/07/202195threeSupervised Contact$219.30$21.93$241.23
    July
    Cell Formulas
    RangeFormula
    I4,I6I4=IF(E4="Activities",0,H4*0.1)
    J4,J6J4=I4+H4
    Cells with Data Validation
    CellAllowCriteria
    F4:J4Any value
  11. I ran LateCancel again
  12. row 5 is the only visible row and it is highlighted and I press Yes to the question.
  13. This is my July sheet
  14. CSS Work Allocation Sheet.77.xlsm
    ABCDEFGHIJ
    1501 CSS July
    2
    3DatePurchase order #Req #NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GST
    4LT CNCL 5/07/202195oneSupervised contact$219.30$21.93$241.23
    55/07/202195twoSupervised Contact$219.30$21.93$241.23
    6LT CNCL 5/07/202195threeSupervised Contact$219.30$21.93$241.23
    July
    Cell Formulas
    RangeFormula
    I4,I6I4=IF(E4="Activities",0,H4*0.1)
    J4,J6J4=I4+H4
    Cells with Data Validation
    CellAllowCriteria
    F4:J4Any value

If the code was working correctly, all three rows should have the "LT CNCL" infront of the date.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Try stepping through the code one line at a time using the F8 function key to see if you can detect where the code is not doing what you expect it to do. It is difficult to troubleshoot the code from here with a mock up file that would replicate your file. You can open the vb editor and size that window so you can see the worksheet at the same time, then step through the code and observe what happens on the worksheet as each line of code executes. When the results are not as expected, note that line of code and then we can examine that and maybe ix the problem.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
As described in post 4, I tried running the transfer sub 3 times and the third time it didn't give the desired results. I get the error message overflow and it highlights this part of the line
VBA Code:
: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count

VBA Code:
Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

@dpaton05
If you press the Windows key AND the right arrow key you will be able to align the windows so you can see what is happening as you step through the code
AND
If you are getting an Overflow error, I'm thinking there is nothing in the rows beyond row 4
VBA Code:
Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for that tip Michael, I have described what it does in post 4. The code runs correctly except on the third time, the code stops at the point described in post 6. Not sure if I have changed my code since I first posted it so here it is again.

VBA Code:
Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, AutoFilterCounter As Long
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        Dim SheetCounter As Integer: SheetCounter = 0
'Call TurnOffFunctionality
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt           ' autofilter for the value in cell [B27]
                                .AutoFilter 3, Req          ' autofilter for the value in cell [B25]
                                                                    'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet.
                                    If ws.[A3].Cells.Offset(1, 0) = "" Then
                                        .AutoFilter
                                        SheetCounter = SheetCounter + 1
                                        'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                        If SheetCounter = 12 Then
                                            MsgBox "A job with the date and request number entered does not exist"
                                        End If
                                        GoTo SkipSheet
                                    End If
                                    
                                'Check the count Autofilter
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                'If value less than 2, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then
                                    .AutoFilter
                                    'Add 1 to a sheet counter
                                    SheetCounter = SheetCounter + 1
                                    'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know
                                    If SheetCounter = 12 Then
                                        MsgBox "A job with the date and request number entered does not exist"
                                    End If
                                    GoTo SkipSheet
                                End If
                                
                                Dim LastRow As Long
                                LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                                Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow)
                                
                                Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count
                                'if this range is greater than 1, ask the below question, else continue
                                If rws > 1 Then
                                'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then
                                    Dim answer As Integer, n As Integer
                                    Dim RowNumber As Long
                                    Dim RowLine As Range
                                    Application.ScreenUpdating = True
                                    n = 0
                                    For Each RowLine In rng.SpecialCells(xlCellTypeVisible)
                                        ws.Activate
                                        RowLine.EntireRow.Interior.ColorIndex = 6
                                        answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job")
                                        RowLine.EntireRow.Interior.ColorIndex = 0
                                        If answer = vbYes Then
                                            'Copy and paste the selected row
                                            RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                            RowLine.EntireRow.Delete
                                            'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them
                                            'RowNumber = RowLine.Row - 3
                                            GoTo FoundRightJob
                                        End If
                                        If answer = vbNo Then n = n + 1
                                    Next RowLine
                                If n > 0 Then MsgBox " You have chosen not to cancel any of the " & n & " job/s !!"
                                End If
                                
FoundRightJob:

                                .AutoFilter                 ' turn off the autofilter
                        End With
                End If
SkipSheet:
        Next ws


'sh.Range("B25,B27").ClearContents
'Call TurnOnFunctionality
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The code in post 6 must have something wrong with it. Maybe I need a different way to count the visible rows?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I don't see any glaring error in the code, but my eyesight is not waht it used to be, and as I said, it is difficult to try and trouble shoot code against a file from a remote location. I have no desire to try and replicate your files to debug the program because the is a 99% chance that I would not exactly duplicate what you have, even with the additional infor in the private message. You can step through the code using the F8 function key and see it the last row variable is less than 4 at the time that line of code executes. Which i stronly susperct it is, but cannot find where it LastRow variable would lose or change values. As a test, you could move the Set statement for the rng variable as shown below and see if that makes a difference. If not then, I wouold zero in on the LastRow variable as being the problem.

VBA Code:
 Set rng = ws.Range("A4:A" & LastRow)
  For Each RowLine In rng.SpecialCells(xlCellTypeVisible)
         ws.Activate
        RowLine.EntireRow.Interior.ColorIndex = 6
 

Watch MrExcel Video

Forum statistics

Threads
1,127,803
Messages
5,626,990
Members
416,213
Latest member
neflerine

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