• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Used Column range

senthil murugn

New Member
Hi,

Need a macro to count the no of specific task name (strings) in Column A.
When used the following code it is working fine but takes too much time (go over the excel's maximum column count)

Set myrange = Worksheets("Sheet1").Range("A:A")

But when used the following code it ends up with error.
Set myrange = Worksheets("Sheet1").UsedRange.Columns("A")

Could you please provide any other alternative workaround.

Thanks & regards
Senthil
 

Attachments

Belleke

Well-Known Member
Code:
Sub Task_Cnt()

Dim ANSYS As Integer, MATLAB As Integer, MPASM As Integer

For Each c In Sheets("Sheet1").UsedRange.Columns(1).Cells
    If c.Value = "Ansys" Then ANSYS = ANSYS + 1
    If c.Value = "Matlab" Then MATLAB = MATLAB + 1
    If c.Value = "Mpasm" Then MPASM = MPASM + 1
Next c

MsgBox "# of Ansys: " & (ANSYS)
MsgBox "# of Matlab: " & (MATLAB)
MsgBox "# of Mpasm: " & (MPASM)

End Sub
 

Marc L

Excel Ninja
But when used the following code it ends up with error.
Set myrange = Worksheets("Sheet1").UsedRange.Columns("A")
Hi, no such error on my side with your attachment and as it is the right way to proceed even if the Range variable is useless …​
 

Marc L

Excel Ninja
According to the attachment an Excel basics VBA demonstration :​
Code:
Sub Demo1()
             Dim V, S$
    With Sheet1.UsedRange.Columns
        For Each V In [{"Ansys","Matlab","Mpasm"}]
            S = S & V & " : " & Application.CountIf(.Item(1), V) & vbLf & vbLf
        Next
    End With
        MsgBox S
End Sub
Do you like it ? So thanks to click on bottom right Like !
 

Belleke

Well-Known Member
According to the attachment an Excel basics VBA demonstration
This remark pisses me off, maybe in Paris it is basic, for most users this is not basic. Respect please!
 

Marc L

Excel Ninja
This remark pisses me off
Cool down big head ‼ There is no remark about you within my previous post, do not misread, thanks !​
According to TEBV rule, for good enough readers only :rolleyes: :​
 
Top