VBA Used Column range

[senthil murugn]

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

 

[Belleke]

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

 

[senthil murugn]

Hi,

Yep it's working fine. Great work

thanks
senthil

 

[Belleke]

Glad to help.

 

[Marc L]

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]

According to the attachment an Excel basics VBA demonstration :​

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]

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]

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 :​

Match Mentors to Mentees (With large test data)

Hi all, I have been surfing the internet to find relevant VBA modules to fulfill my needs. Unfortunately, I was unable to find a solution. Normally, I would have 100 mentors and 1300 mentees (roughly, 1 mentor to 13 mentees). I hope I can generate a list of matching results. (See the...

chandoo.org

Excel vba to duplicate copy and paste rows for multiple times

Hi All, can help me with a macro to duplicate rows based on specific cell value in column A? For example, i have data in 10 rows, then I need the line to be duplicated so it appears 7 times. per number so total row will be 70. Enclosed file for reference worked code pasted below >>> You...

chandoo.org

How to round numbers based on column value using VBA ?

How to round numbers based on column value using VBA ? EX. Column A is Name (person) , B(SCORE) list of score =(12.5 ,6.25,62.5,6.25 and 12.5) , C (expected value after round) =13,6,6,63,12) respectively. i.e sum of B and C should be equal.

chandoo.org