Using a macro to delete all blank rows

[Shirley Munro]

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

 

[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
(e-mail address removed)
"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...

 

[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
(e-mail address removed)

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
(e-mail address removed)
"Shirley Munro"

wrote in message

 

[Don Guillett]

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