CODEWITHSUNDEEP
excelvba
remy
remy

Reputation: 37

Count rows based on text in cell

I was tasked to make a Userform in Excel to, at a click of a button,

This code counts however much there is for all five types. It is unable to count the remaining such as, the total amount of rows including both hidden and unhidden, total amount of hidden rows, total amount of rows where it does not contain any of the five data types.

Private Sub CommandButton5_Click()

Columns("P:x").ColumnWidth = 27.5
Columns("P:x").HorizontalAlignment = xlCenter
Columns("p:x").VerticalAlignment = xlCenter
Dim R As Long
Dim L As Long
Dim N As Long
Dim P As Long
Dim O As Long
Dim A As Long
Dim F As Long
Dim G As Long
Dim col As Range, i As Integer

Dim c As Long
Dim MyRange As Range
Dim myCell As Range
Dim M, range_1 As Range
Set range_1 = Range("J1").EntireColumn
With range_1
    R = Worksheets("Default").Cells(Rows.Count, "A").End(xlUp).Row

    For L = 2 To R
        If Worksheets("Default").Cells(L, "J") = "Incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
            N = N + 1
        End If
        If Worksheets("Default").Cells(L, "J") = "To be incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
            M = M + 1
        End If
        If Worksheets("Default").Cells(L, "J") = "Won't be incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
            O = O + 1
        End If
        If Worksheets("Default").Cells(L, "J") = "Partially incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
            P = P + 1
        End If
        If Worksheets("Default").Cells(L, "J") = "Replaced by other EA" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
            G = G + 1
        End If
    Next
End With
Worksheets("default").Cells(R, "P") = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count
A = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count
Worksheets("Default").Cells(R, "s") = N
Worksheets("Default").Cells(R, "R") = M
Worksheets("Default").Cells(R, "U") = O
Worksheets("Default").Cells(R, "q") = N + M + O + P + G
Worksheets("Default").Cells(R, "T") = P
Worksheets("Default").Cells(R, "V") = G
Worksheets("Default").Cells(R, "w") = (ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count - (N + M + O + P + G)) - 1
Worksheets("Default").Cells(R - 1, "s") = "EAs that are incorporated"
Worksheets("Default").Cells(R - 1, "R") = "EAs that are To be Incorporated"
Worksheets("Default").Cells(R - 1, "U") = "EAs that won't be Incorporated"
Worksheets("Default").Cells(R - 1, "q") = "EAs with an incorporation status"
Worksheets("Default").Cells(R - 1, "T") = "EAs with partially incorporated"
Worksheets("Default").Cells(R - 1, "w") = "EAs without incorporation status"
Worksheets("Default").Cells(R - 1, "P") = "Visible EAs"
Worksheets("Default").Cells(R - 1, "x") = "Non-visible EAs"
Worksheets("Default").Cells(R - 1, "V") = "EAs Replaced by other EA"
Worksheets("Default").Cells(R, "x") = R - A - 1
MsgBox A

End Sub

Upvotes: 0

Views: 67

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

This part is very ineficcient.

For L = 2 To R
    If Worksheets("Default").Cells(L, "J") = "Incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
    N = N + 1
    End If
    If Worksheets("Default").Cells(L, "J") = "To be incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
    M = M + 1
    End If
    If Worksheets("Default").Cells(L, "J") = "Won't be incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
    O = O + 1
    End If
    If Worksheets("Default").Cells(L, "J") = "Partially incorporated" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
    P = P + 1
    End If
     If Worksheets("Default").Cells(L, "J") = "Replaced by other EA" And (Worksheets("Default").Rows(L).EntireRow.Hidden = False) Then
    G = G + 1
    End If    
Next

Imagine Cells(L, "J") is Incorporated why would you still go further and check if it is all the others if you already know they cannot be. Your code just wastes time then.

For L = 2 To R
    If Worksheets("Default").Rows(L).EntireRow.Hidden = False Then
        Select Case Worksheets("Default").Cells(L, "J")
            Case "Incorporated":            N = N + 1
            Case "To be incorporated":      M = M + 1
            Case "Won't be incorporated":   O = O + 1
            Case "Partially incorporated":  P = P + 1
            Case "Replaced by other EA":    G = G + 1
        End Select
    End If
Next L

So this would first check if the row is not hidden. And only then check the value of column J. Once Select Case finds a value it stops checking for the others.

To your counting issue:

Do not use ActiveSheet instead specify the sheet you mean by name. Do never let Excel guess which sheet to look at, the active sheet might change by a single mouse click during code execution.

The issue with your counting is that the following

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count

will give you only the amount of rows of the first area of a non-continous range.
If you need to know the number of all visible rows you need either loop through all areas and sum up.

Or as a workaround you can do:

ActiveSheet.UsedRange.Resize(ColumnSize:=1).SpecialCells(xlCellTypeVisible).Cells.Count

The trick here is to limit the used range to one column using Resize(ColumnSize:=1) and count the visible cells there (which is the same number as visible rows) but with the difference that this is counting the total number over all areas!

Upvotes: 1

Related Questions