I am recording a macro which includes taking a text string and using th
mid function to extract 7 characters from this string. Once I hav
performed the function, I then need to copy it down the remainin
cells. This works fine on the current download of my file, bu
subsequent downloads vary in terms of the number of rows. I have trie
copying the formula down to row 65,536 to ensure it picks up all activ
rows but now I am left with thousands of blank rows which I would lik
to delete. Is there a way of copying the formula to the last activ
row or is there a way of deleting all rows after the last active row.

Thanks

Shirle

--
Shirley Munr
-----------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...info&userid=83
View this thread: http://www.excelforum.com/showthread.php?threadid=42613

Delete all blank rowsSAVE then modify this to suit

Set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "=h7+d8" 'your mid formula
.Formula = .Value
End With

--
Don Guillett
SalesAid Software

"Shirley Munro"
wrote in message
news:Shirley.Munro.1uxryh_1126008363.3904@excelfor um-nospam.com...

I am recording a macro which includes taking a text string and using the
mid function to extract 7 characters from this string. Once I have
performed the function, I then need to copy it down the remaining
cells. This works fine on the current download of my file, but
subsequent downloads vary in terms of the number of rows. I have tried
copying the formula down to row 65,536 to ensure it picks up all active
rows but now I am left with thousands of blank rows which I would like
to delete. Is there a way of copying the formula to the last active
row or is there a way of deleting all rows after the last active row.

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=426138

Shirley,

You did not post your coding efforts for comments. This will do exactly what
you want by copying the formula down to the last row by looking from the
bottom up. You can also look from the bottom down by using
range("h8").end(xldown).row. Then the formula is converted to a value
thereby saving valuable resources. So you use only the rows necessary. To
get rid of the overload you have already created, delete all rows below the
last item in col A and SAVE. Then put this macro in a module and execute
from alt f8 or assign to a button. If you still can't figure it out send me
a file to my personal address. Do NOT attempt to attach to a newsgroup
message. BAD netiquette.

sub getmidinfo()
Set frng = Range("h2:b" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "=mid(h2,7,5)" 'your mid formula
= .Value
End With
end sub
=======
Shirley sent this message to me privately.
HI Don

thanks for your reply but I am not particularly experienced in programming
and I don't really understand your instructions and I am not sure if I have
made my question clear. I can perform the Mid function fine but the number
of cells it has to be copied into varies each time the file is downloaded.
On a previous question to the forum, it was suggested that the formula is
copied down to the last remaining cell in the appropriate column. This is
fine but it also means that the file thinks there are 65,536 rows in use and
this is no use for the next step of the macro I am recording. The mid
function is being performed in column B on the text in column A and I want
it to be copied into all remaining cells down column B where there is a
value in column A. If I go with copying the formula down to cell B65,536
then I am left with thousands of blank rows which I need to delete. It is
this that I don't know how to do but you may have a better suggestion on how
to solve the problem.

Thanks
==========

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Delete all blank rowsSAVE then modify this to suit

Set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "=h7+d8" 'your mid formula
.Formula = .Value
End With

--
Don Guillett
SalesAid Software

"Shirley Munro"

wrote in message
news:Shirley.Munro.1uxryh_1126008363.3904@excelfor um-nospam.com...

I am recording a macro which includes taking a text string and using the
mid function to extract 7 characters from this string. Once I have
performed the function, I then need to copy it down the remaining
cells. This works fine on the current download of my file, but
subsequent downloads vary in terms of the number of rows. I have tried
copying the formula down to row 65,536 to ensure it picks up all active
rows but now I am left with thousands of blank rows which I would like
to delete. Is there a way of copying the formula to the last active
row or is there a way of deleting all rows after the last active row.

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=426138

of course the line
= .Value
should read
..value=.value

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Shirley,

You did not post your coding efforts for comments. This will do exactly
what
you want by copying the formula down to the last row by looking from the
bottom up. You can also look from the bottom down by using
range("h8").end(xldown).row. Then the formula is converted to a value
thereby saving valuable resources. So you use only the rows necessary. To
get rid of the overload you have already created, delete all rows below
the
last item in col A and SAVE. Then put this macro in a module and execute
from alt f8 or assign to a button. If you still can't figure it out send
me
a file to my personal address. Do NOT attempt to attach to a newsgroup
message. BAD netiquette.

sub getmidinfo()
Set frng = Range("h2:b" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "=mid(h2,7,5)" 'your mid formula
= .Value
End With
end sub
=======
Shirley sent this message to me privately.
HI Don

thanks for your reply but I am not particularly experienced in programming
and I don't really understand your instructions and I am not sure if I
have
made my question clear. I can perform the Mid function fine but the
number
of cells it has to be copied into varies each time the file is downloaded.
On a previous question to the forum, it was suggested that the formula is
copied down to the last remaining cell in the appropriate column. This is
fine but it also means that the file thinks there are 65,536 rows in use
and
this is no use for the next step of the macro I am recording. The mid
function is being performed in column B on the text in column A and I want
it to be copied into all remaining cells down column B where there is a
value in column A. If I go with copying the formula down to cell B65,536
then I am left with thousands of blank rows which I need to delete. It is
this that I don't know how to do but you may have a better suggestion on
how
to solve the problem.

Thanks
==========

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Delete all blank rowsSAVE then modify this to suit

Set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "=h7+d8" 'your mid formula
.Formula = .Value
End With

--
Don Guillett
SalesAid Software

"Shirley Munro"

wrote in message
news:Shirley.Munro.1uxryh_1126008363.3904@excelfor um-nospam.com...

I am recording a macro which includes taking a text string and using
the
mid function to extract 7 characters from this string. Once I have
performed the function, I then need to copy it down the remaining
cells. This works fine on the current download of my file, but
subsequent downloads vary in terms of the number of rows. I have
tried
copying the formula down to row 65,536 to ensure it picks up all
active
rows but now I am left with thousands of blank rows which I would like
to delete. Is there a way of copying the formula to the last active
row or is there a way of deleting all rows after the last active row.

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=426138

I tried the coding and could not get it to work. Anymore help you can
give me would be much appreciated. I tried the minor coding changes
that you sent also but no luck - I got a run time error.


--
Shirley Munro
------------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
View this thread: http://www.excelforum.com/showthread...hreadid=426138