VBA Macro formatting question

[salvadorbali]

Dear Excel Pros,

I'm trying to record a macro and there is something in which I would need your advise.
I filtered down one column, and I would like to copy all values from column A to column B.
When I'm doing it manually, I put a =A2 formula in the B2 cell, and drag down the formula until the end of contents.
Obviously, I see where the content ends (usually, this does not cost a lot of scrolling, as my tables contain
no more than 100 lines).
The problem is that I want my VBA to detect automatically where the contents of column A end,
and copy its contents in column B only until that certain point/line.

I already found a formula which detects the end of contecnts a certain column,
but here it's more comlicated, as I need the VBA to detect the end of ANOTHER column, not
the one i'm applying my formula.

Do you have any idea which formula I could use to detect this?


Thanks in advance for any advise!

 

[Special-K99]

I presume you mean you want something like this:

a     f
b     g
c     h
d
e

OUTPUT:

a     f
b     g
c     h
d     a
e     b
      c
      d
      e

Attach a sample spreadsheet, remove any sensitive data.

You cant attach files on this forum.
Upload the file to an online storage site then place a link to it on this forum.

 

[salvadorbali]

Dear Special-K99,

No, actually I do not have any data in my B column, it is a blank newly inserted column. I only need to copy the (filtered) data from column A.
The problem is only the drill-down. As this is a filtered column, I use the manual drill-down when I'm doing the copy manually.
How can I have the same effect if I want to automate it (with Macro)?

I hope I made myself clear.