EXCEL MARCO, HELP, URGENT

1379

2008-06-24 5:57 am

Please help how to write an Marco to do the following.
When using autofilter, the first row is the heading. I want to copy only the result after the auto-filter to another worksheet.
I cannot use copy all function as it will copy the column header as well.
If use the 1 row down, it doesn't know which row to start as the filter result is not the same every time.
How can I write it?

回答 (1)

Hilda

2008-06-26 9:00 pm

✔ 最佳答案

Assume you have 3 columns of data, after using the autofilter,
you are ready to run a macro to copy the result set.
In the macro below, based on the data of one of the columns,
(I used column B) it counts the number of rows of data to be copied.

Sub Macro1()
Dim i As Long
Dim mStr As String
i = 2 'comment: start with the 2nd row
While Cells(i, 2) <> ""
i = i + 1
Wend
If i = 2 Then
'comment: when there is no data listing, copy a blank row of data
mStr = "A2:C2"
Else
mStr = "A2:C" & i - 1
End If
Range(mStr).Select
Selection.Copy
End Sub

Hope this help.

👍 0 👎 0