Sort and filter macro

[KH_GS]

Hi

I recorded a macro, trying to sort a column and then filter it paste to a new column.

Does anyone find the code exceptionally long? I suspect it can be coded neater but I'm novice.

Please Login or Register  to view this content.

[Ardus Petus]

You can deleye all lines with ActiveWindow.ScrollRow = x

HTH
--
AP

"KH_GS" <[email protected]> a écrit dans le
message de news:[email protected]...
>
> Hi
>
> I recorded a macro, trying to sort a column and then filter it paste to
> a new column.
>
> Does anyone find the code exceptionally long? I suspect it can be coded
> neater but I'm novice.
>
>
>
> Code:
> --------------------
> Sub SortFilter ()
>
> Columns("D:D").Select
> Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 24
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 1
> Columns("D:D").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("F1" _
> ), Unique:=True
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 24
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 1
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 24
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 1
> End Sub
>
> --------------------
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile:
http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527968
>

[Norman Jones]

Hi KH,

Try, perhaps:

'=============>>
Sub SortFilter()

With Columns("D:D")
.Sort Key1:=Range("D2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("F1"), _
Unique:=True
End With

End Sub
'<<=============


---
Regards,
Norman


"KH_GS" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> I recorded a macro, trying to sort a column and then filter it paste to
> a new column.
>
> Does anyone find the code exceptionally long? I suspect it can be coded
> neater but I'm novice.
>
>
>
> Code:
> --------------------
> Sub SortFilter ()
>
> Columns("D:D").Select
> Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 24
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 1
> Columns("D:D").AdvancedFilter Action:=xlFilterCopy,
> CopyToRange:=Range("F1" _
> ), Unique:=True
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 24
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 1
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 24
> ActiveWindow.ScrollRow = 23
> ActiveWindow.ScrollRow = 22
> ActiveWindow.ScrollRow = 21
> ActiveWindow.ScrollRow = 20
> ActiveWindow.ScrollRow = 19
> ActiveWindow.ScrollRow = 18
> ActiveWindow.ScrollRow = 17
> ActiveWindow.ScrollRow = 16
> ActiveWindow.ScrollRow = 15
> ActiveWindow.ScrollRow = 14
> ActiveWindow.ScrollRow = 13
> ActiveWindow.ScrollRow = 12
> ActiveWindow.ScrollRow = 11
> ActiveWindow.ScrollRow = 10
> ActiveWindow.ScrollRow = 9
> ActiveWindow.ScrollRow = 8
> ActiveWindow.ScrollRow = 7
> ActiveWindow.ScrollRow = 6
> ActiveWindow.ScrollRow = 5
> ActiveWindow.ScrollRow = 4
> ActiveWindow.ScrollRow = 3
> ActiveWindow.ScrollRow = 2
> ActiveWindow.ScrollRow = 1
> End Sub
>
> --------------------
>
>
> --
> KH_GS
> ------------------------------------------------------------------------
> KH_GS's Profile:
> http://www.excelforum.com/member.php...o&userid=32920
> View this thread: http://www.excelforum.com/showthread...hreadid=527968
>