Deleting all rows after the last active cell

[Shirley Munro]

I need to download a file on a regular basis and this number of rows in the files varies each time it is downloaded. I am using a macro on column B to carry out the MID function on column A. Once the function has been carried out I then need to copy the formula down all of column B as I don't know what the last active cell will be from one download to the next. The result is that I have thousand of excess rows which I need to delete. Can anyone give me code that will delete all rows from the last active cell onwards.

Thanks

Shirley

[Dave Peterson]

How about just applying the formula to the rows you want.

Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("b1:B" & lastrow).formula = "=mid(yourformulahere)"
end with



Shirley Munro wrote:
>
> I need to download a file on a regular basis and this number of rows in
> the files varies each time it is downloaded. I am using a macro on
> column B to carry out the MID function on column A. Once the function
> has been carried out I then need to copy the formula down all of column
> B as I don't know what the last active cell will be from one download to
> the next. The result is that I have thousand of excess rows which I
> need to delete. Can anyone give me code that will delete all rows from
> the last active cell onwards.
>
> Thanks
>
> Shirley
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
> View this thread: http://www.excelforum.com/showthread...hreadid=469489

--

Dave Peterson

[Shirley Munro]

Hi Dave

Many thanks for your help - your code worked perfectly. The reason I wasn't just applying the formula to the cells I needed was that I wanted it all to run from a macro and now it does.

Shirley