Microsoft Office Forums - View Single Post

excelledsoftware · 06-11-2015, 08:14 PM

Ok I am guessing we are ok to run the regular plan. If you only have a few columns this should actually run pretty quick. I was able to use your attached spreadsheet, duplicate the data 65,000 times and run this code. It took about 8 seconds to complete.
Here is the code

Code:

Option Explicit
Sub CountTrueValues()
  'Inserts a column to the right of C and then runs
  'a countif to see how many times the phrase true
  'is entered in the columns
  
  Dim CheckRow As Long, LastRow As Long, Tot As Integer
  Dim LastCol As String
  
  'Insert a column
  Range("D:D").Insert
  Range("D1").Value = "TRUE Count"
  
  'Identify where to stop
  LastRow = Range("C2").End(xlDown).Row
  LastCol = Range("A1").End(xlToRight).Address
  LastCol = Mid(LastCol, 2) 'remove first $
  LastCol = Mid(LastCol, 1, InStr(1, LastCol, "$") - 1) 'remove the row

  'Go through the data and enter a value
  For CheckRow = 2 To LastRow
    Tot = WorksheetFunction.CountIf(Range("E" & CheckRow & ":" & LastCol & CheckRow), "true")
    Range("D" & CheckRow).Value = Tot
  Next CheckRow
  
  MsgBox "done"
  
End Sub

Let me know if this works or if you want to go about it a different way.