Dear All,
I am using the following code in one of my application.......
Now I requier to repeat this for not less than 15 times for different columns, and depend on that i want to paste it to different sheet in different column.
I know by defining different variable and then using for next loop I can achieve the result but I don't know to write the correct syntax.
Where and what changes neeeded I have written in bracket.
Dim NoDels
Sheets("Data In Final").Select
NoDels = Application.WorksheetFunction.CountIf(Range("B:B"), "#N/A")
( Now Here I have to See every second column starting with B, and then D,F,H
)
If NoDels <> 0 Then
ActiveSheet.Range("$A$1:$DZ$5000").AutoFilter Field:=2, Criteria1:="#N/A"
( Here the filter field will change as For B
2,D
.. 4 and so on
..)
Range("A2:B5000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Data Master").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:B").Select
( here will be the pair A:B,C:D,and so on
.)
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Data Master").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data Master").Sort.SortFields.Add Key:=Range( _
"A2:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
( here It will be A2:A5000,C2:C5000 and so on
)
With ActiveWorkbook.Worksheets("Data Master").Sort
.SetRange Range("A1:B5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
So I need the exact syntax to write.....
Say in first bracket in place of
NoDels = Application.WorksheetFunction.CountIf(Range("B:B"), "#N/A")
I want to write a for next loop which changes the column B to Z
Jayesh.