Removing rows via macro/VBS script

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a worksheet that creates journal files for our financial
system for upload. Currently we have basic macros to do repetitive
operations such as unhide sheets and remove columns.

The worksheet has a default 85 rows and is populated to equal or less than
85 rows. I want to create a macro whereas when the first row reaches no more
input to truncate all rows below it.

For example, column B is the account field, if row 23 is the last line of
the journal load, I need row 24 to 85 to be deleted.

Not sure if a regular macro or visual basic can be used. If VB, can someone
give a sample script? Thanks
 
Dim rng as Range
set rng = cells(rows.count,2).End(xlup)(2)
if rng.row <= 85 then
Range(rng,Range("B85")).EntireRow.Delete
end if
 
This appears to delete all but row 1 if the data is filled to row 85 - just
a heads up. (if that will never happen, then not a concern)
 
Tom,

Thanks for the help. Looking at the code you have provided (I am no VBS
guru) but this code appears that if it is less then 85 rows it will delete
all the rows.

I think I did not communicate this well. The form has an established 85
rows. If a journal load contains 45 rows of the 85, then I want the system
to find the first 0 (zero) in Column G of the worksheet. This row and all
below must be deleted.

Thanks again for all the help
Jeff
 
Back
Top