VBA Statusbar Row Counter

[Stephen_IV]

Is there some code that would help me count in the statusbar every row that the code wen thru?

Thanks in advance Stephen.

 

[wigi]

Yes.

What variable are you using to loop through the rows?

 

[kpark91]

Hi Stephen.

For i = 1 to 100
    Range("A" & i).Value = "Blarg"
    Application.StatusBar =  i
Next i

To return the statusbar to normal,

Application.StatusBar = False

 

[Stephen_IV]

Here is the code that I am using

Option Explicit

Public Sub ParseOutDates()

Dim r As Range
Set r = Sheet1.UsedRange

Dim row As Range

Dim first As Boolean
first = True

For Each row In r.Rows
    If Not first Then
        If Trim(row.Cells(1, 1).Text) = "" Then Exit For
        If Trim(row.Cells(1, 2).Text) = "" Then Exit For
        If Trim(row.Cells(1, 3).Text) = "" Then Exit For
        
        Dim date1 As Date
        Dim date2 As Date
        Dim reason As String
        
        date1 = row.Cells(1, 1)
        date2 = row.Cells(1, 2)
        reason = row.Cells(1, 3)
        
        Dim i As Long
        Dim j As Long
        For i = 4 To r.Columns.Count
            If Trim(r.Cells(1, i).Text) = "" Then Exit For
            
            Dim dateI As Date
            dateI = r.Cells(1, i)
            
            If dateI > date2 Then Exit For
            
            If dateI >= date1 And dateI <= date2 Then
                row.Cells(1, i).Value = row.Cells(j + 1, 3).Value
            End If
        Next
    End If
    first = False
Next

End Sub

 

[wigi]

The use of a variable called "row" is a bad choice, but if you want, this could work:

Option Explicit

Public Sub ParseOutDates()

Dim r As Range
Set r = Sheet1.UsedRange

Dim row As Range

Dim first As Boolean
first = True

For Each row In r.Rows
    If Not first Then
       Application.StatusBar =  row.Row
       'code
    End If
Next
Application.StatusBar = False
End Sub

 

[brettdj]

A couple of comments

1) Your code runs on each row, but then never processes the main code portion as First = True. So two sub comments.
a) Put the First test outside the loop to avoid redundant testing of each row, which in your current code case means exiting the entire code immediately (as nothing happens if first = true)

If first Then Exit Sub
For Each row In r.Rows
 If Trim(row.Cells(1, 1).Text) = "" Then Exit For

...
Next

b) Consider calling the ParseOutDates with a Boolean argument if the flag is set by code

2) When you are using a counter to flag progress it best to work in % terms complete, or at a meaningful interval level rather than updating the statusbar for every record which will be a flickering distraction. Especially if you were running a userform progress bar rather than a statusbar update. If the code is very short on run time then the statusbar counter is redundant in the first place

This code runs the update - in overall completion terms assuming no early exit - on every 100th record using a Mod test

I'd also be wary re using the Sheet1.UsedRange approach (in your current code). Instaed use a sheet name, sheet position, or using the parent of a range object

 Dim rng1 As Range
    Set rng1 = Sheets(1).UsedRange
    Dim r As Range
    For Each r In rng1.Rows
        If r.row Mod 100 = 0 Then Application.StatusBar = Format(r.row / rng1.Rows.Count, "#0.0%")
    Next

Cheers

Dave

 

[Stephen_IV]

Thanks Wigi and Dave for all your help and comments. I appreciate it and look into every that you both suggested. Thank again!

 

[vicsar]

Hi,

If you only want to display the row number on which you are located you could used something way simpler:

Application.StatusBar = "Analyzing row " & ActiveCell.Row

- Remember to always return control of the status bar to Excel with:
Application.StatusBar = False

Here is some sample code that youcan use to test:

Sub RemoveEmptyRows()

' Go to las value in table and check to remove emty rows
    Range("C1").Select
    Selection.End(xlDown).Select
    
    Do
    If ActiveCell.Value = "           " Then
        Application.StatusBar = "Analyzing row " & ActiveCell.Row
        Selection.EntireRow.Delete
' Offset to the previous row
        ActiveCell.Offset(-1, 0).Select
        
    Else
' Return status bar control to Microsoft Excel
    Application.StatusBar = False
        Exit Do
    End If
    Loop

End Sub