Hi All,

I have calling some macro like this

Call Duplicate_Delete

The Macro "Duplicate_Delete" deletes the col A's duplicate data
using Countif function.

Once this done again my main macro does some activity

Again i need to run the Duplicate_Delete macro for the Col H.

but previous i coded as
Application.WorksheetFunction.CountIf(Range("A1:A" & x),
Range("AY").Text).

So, i need to run the same code for Col H.

I know, i need to create new macro which hardly contains 3 lines.

Is there any option i can assign it as follows

Application.WorksheetFunction.CountIf(Range(ColA & "1:" & ColA & x),
Range("AY").Text).

Eg.,

Cola = "A"
Call Duplicate_Delete

''
do other work

''

Cola = "H"
Call Duplicate_Delete

Thanks in advance

Yes

Dim DeleteCol as string
Dim CompareStr as string
DeleteCol = "H"
CompareStr = Range("AY").Text
Call Duplicate_Delete(DeleteCol,CompareStr)


sub Duplicate_Delete(DeleteCol as string, CompareStr as String)
Application.WorksheetFunction.CountIf( _
Range(DeleteCol & "1:" & DeleteCol & x),CompareStr)
end sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199734

http://www.thecodecage.com/forumz

Try the below. Do you mean Range("AY").Text) ?

Dim strCol As String
strCol = "H"

varCount = Application.WorksheetFunction. _
CountIf(Columns(strCol), Range("A1").Text)

--
Jacob (MVP - Excel)


"fi.or.jp.de" wrote:

Hi All,

I have calling some macro like this

Call Duplicate_Delete

The Macro "Duplicate_Delete" deletes the col A's duplicate data
using Countif function.

Once this done again my main macro does some activity

Again i need to run the Duplicate_Delete macro for the Col H.

but previous i coded as
Application.WorksheetFunction.CountIf(Range("A1:A" & x),
Range("AY").Text).

So, i need to run the same code for Col H.

I know, i need to create new macro which hardly contains 3 lines.

Is there any option i can assign it as follows

Application.WorksheetFunction.CountIf(Range(ColA & "1:" & ColA & x),
Range("AY").Text).

Eg.,

Cola = "A"
Call Duplicate_Delete

''
do other work

''

Cola = "H"
Call Duplicate_Delete

Thanks in advance
.

Oops...I missed the point..Refer Joel's post

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Try the below. Do you mean Range("AY").Text) ?

Dim strCol As String
strCol = "H"

varCount = Application.WorksheetFunction. _
CountIf(Columns(strCol), Range("A1").Text)

--
Jacob (MVP - Excel)


"fi.or.jp.de" wrote:

Hi All,

I have calling some macro like this

Call Duplicate_Delete

The Macro "Duplicate_Delete" deletes the col A's duplicate data
using Countif function.

Once this done again my main macro does some activity

Again i need to run the Duplicate_Delete macro for the Col H.

but previous i coded as
Application.WorksheetFunction.CountIf(Range("A1:A" & x),
Range("AY").Text).

So, i need to run the same code for Col H.

I know, i need to create new macro which hardly contains 3 lines.

Is there any option i can assign it as follows

Application.WorksheetFunction.CountIf(Range(ColA & "1:" & ColA & x),
Range("AY").Text).

Eg.,

Cola = "A"
Call Duplicate_Delete

''
do other work

''

Cola = "H"
Call Duplicate_Delete

Thanks in advance
.

Joel, Thank u.

It works great


On Apr 30, 6:36*pm, joel wrote:
Yes

Dim DeleteCol as string
Dim CompareStr as string
DeleteCol = "H"
CompareStr = Range("AY").Text
Call Duplicate_Delete(DeleteCol,CompareStr)

sub Duplicate_Delete(DeleteCol as string, CompareStr as String)
Application.WorksheetFunction.CountIf( _
Range(DeleteCol & "1:" & DeleteCol & x),CompareStr)
end sub

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=199734

http://www.thecodecage.com/forumz

Joel,

Give me some tips to learn excel vba.

thanks for your help


On Apr 30, 6:36*pm, joel wrote:
Yes

Dim DeleteCol as string
Dim CompareStr as string
DeleteCol = "H"
CompareStr = Range("AY").Text
Call Duplicate_Delete(DeleteCol,CompareStr)

sub Duplicate_Delete(DeleteCol as string, CompareStr as String)
Application.WorksheetFunction.CountIf( _
Range(DeleteCol & "1:" & DeleteCol & x),CompareStr)
end sub

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=199734

http://www.thecodecage.com/forumz