Using a macro to delete all blank rows

[Shirley Munro]

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

[Don Guillett]

Delete all blank rows>SAVE> 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
[email protected]
"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Don Guillett]

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
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> Delete all blank rows>SAVE> 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
> [email protected]
> "Shirley Munro"
<[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
> > Delete all blank rows>SAVE> 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
> > [email protected]
> > "Shirley Munro"
> <[email protected]>
> > wrote in message
> > news:[email protected]...
> > >
> > > 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 Munro]

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.