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

Range("B85",Range("B85").End(xlUp)(2)).Entirerow.D elete

--
HTH,
Bernie
MS Excel MVP


"ssciarrino" wrote in message
...
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

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)


--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Range("B85",Range("B85").End(xlUp)(2)).Entirerow.D elete

--
HTH,
Bernie
MS Excel MVP


"ssciarrino" wrote in message
...
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

--
Regards,
Tom Ogilvy

"ssciarrino" wrote in message
...
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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"ssciarrino" wrote in message
...
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