Hi Everyone,

I am relatively new to vb and as such have been using the macro recorder to help me take my baby steps along the way. As such my code is exeptionally slow. I have been given the advice that to speed it up use variables instead of cells in the workbook itself. I am trying to get my countif function working using variables, however I have gotten stuck trying to count the total number of rows in the worksheet. I have done this before successfully and have literally copied code across but it's not working. Can anyone provide some help on what's going wrong? I have posted my code below.

Any other feedback or tips on how to get my code running faster would be much appreciated.

Sub Countif_trial()

Dim x As Long, var1 As Long, var2 As String

Sheets("Sheet2").Select
var2 = Range("A2").Value

With ThisWorkbook.Sheets("Sheet1")

x = .Cells(.Rows.count, 1).End(x1Up).Rows.count
var1 = Application.WorksheetFunction.CountIf(Range("A2:A" & x), var2)

End With


MsgBox var1


End Sub

Thanks,

Matt

Hi

You can try using


i1 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row

to get the last row

Also you can try any of the methods listed below for improving macro performance

http://vbadud.blogspot.in/search?q=Speed

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Thanks for the reply. It's moving much faster.

I have run into another problem with the countif returning 0. I also have the same formula ie =countif(A2:A5000,D1) which comes back with 4. Is there a reason that might happen? Perhaps the string isn't working in that instance? I have been returning the string and it seems to be the correct value.

Any help is much appreciated, Thanks,

Matt

Hi Matt

My wild guess is the code is taking the values from the another workbook/sheet that might be active

You can avoid this by adding

or

etc

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)