0

bst365体育投注网址大全I am trying to build a macro and I want to copy a row after selecting a specific cell with the filter. So I filter a cell in column "A" and then I want to copy all the cells in that row. The problem is that the rownumber corresponding to the selected cell may change, due to different datasets.

Below is the code:

Selection.AutoFilter
    ActiveSheet.Range("$A$1:$DO$46").AutoFilter Field:=2, Criteria1:= _
        "NAME"
    Range("A5:DO5").Select
    Selection.Copy

The point is that in VBA the selection is set at "A5:DO5", because in this specific dataset "NAME" is on "A5".

But in a different dataset "NAME" might be on "A9", but in VBA the selection of the row is still on "A5:DO5".

How can I make the selection of "NAME" and the copy of all the cells in the row of "NAME" linked to eachother?

0
0

bst365体育投注网址大全I think the question could be a bit clearer, but here goes.

bst365体育投注网址大全As I understand it you want to copy a row, say 5th row down, after a filter has been applied to hide some rows in between (5th visible row). Or, you want to find "NAME" and copy the corresponding row, irrespective of what's hidden and what isn't.

(If I haven't got the above right, please let me know and/or consider clarifying the question)

Try something like this;

Dim C as Cell
Dim RowNum as Integer 'If you're looking for the 5th visible cell.

For each C in Range("A:A").SpecialCells(xltypeVisible)
    RowNum = RowNum+1 'Count how many visible cells you've looped
    If C.Value = "NAME" Then
    'Or for the 5th visible cell, use 'If RowNum = 5 Then'
        C.EntireRow.Copy
        Exit For
    End If
Next
| improve this answer | |
0
0

bst365体育投注网址大全Thank you for your answer. Unlukily, the code is not working.

bst365体育投注网址大全To clarify:

I want to find "NAME" in Column "B" and copy the corresponding row of "NAME", irrespective of what's hidden and what isn't.

bst365体育投注网址大全Now I have this code:

Dim C As Range
For Each C In Range("B:B")
If C.Value = "NAME" Then
C.EntireRow.Copy
Exit For
End If
Next

bst365体育投注网址大全The code does copy a row, but not the row that corresponds with "NAME". Instead, it copies the first row in the active sheet.

| improve this answer | |
  • I will edit my original answer now that I understand the question. Thanks for that. – Spencer Barnes Jul 2 at 14:06
  • Important note here, does the cell you're looking at contain the quotation marks, or were you just putting them in for grammar? Are we searching for NAME or "NAME" ? – Spencer Barnes Jul 2 at 14:10
  • Because in order to insert quotation marks in a string they have to be doubled, to show they're part of the string rather than defining the start and end of the string – Spencer Barnes Jul 2 at 14:11
  • Thank you for your answers. I solved it with another solution: Range("B:B").Find("NAME").Select ActiveCell.EntireRow.Select Selection.Copy This way it works out fine! – Franco yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.