Adjusting last row with total

[ysherriff]

to all, good afternoon.

i have a macro that copies data into a template and then delete any rows that do not have data before generating the report. the last row of the templat has a total row which sums up the certain columns. but when i run my generator, it deletes this row... i believe this section of my code needs to be modified but don't know where to start.

Range("START_CELL").Select
Selection.End(xlDown).Select
rownum = Selection.Row
Range("b" & rownum + 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

i have attached the template. you will see the last row has total for calculation as well as the code module which is called "mod_generate_workbook"

Thank you

 

[prasaddn]

Hi,

I just read your scenario, did not open the files.

Assuming you have column b with maximum number of required data rows.
And, also assuming your file would not be more than 65000 rows, if more than according change this value.

Instead of above code, try this:
rownum = Range("B65000").End(xlUp).Row + 1
Range("B"& rownum & ":" Range("B"& rownum).End(xlDown).ClearContents

note:
you can use delete as well, but it will be slower.

Range("B"& rownum & ":" Range("B"& rownum).End(xlDown).EntireRow.Delete

Regards,
Prasad DN

 

[ysherriff]

thank you very much. will try

 

[ysherriff]

it gives me a syntax error for some reason on this line

Range("B"& rownum & ":" Range("B"& rownum).End(xlDown).EntireRow.Delete

 

[ysherriff]

also in reviewing the code. this is not what i need. i created a template that will be used to generatedividual workbooks. the template maximum row is 30. what this means is that some data when the workbook is being generated can be less than 30 rows but no more. the last row, in this case, has the total.

my code above works perfectly but it deletes my last row which has the totals. all i want it to do is search until it gets to the total row, delete all empty rows above it and then move the total row up. when you see the template you will see what i mean. i have attached again.

 

[prasaddn]

Hi

sorry about that. I should have reviewed your code/files. Again, I have some issue here not able to open the files.

to revised line is :
Range("B" & rownum & ":B" & Range("B" & rownum).End(xlDown)).EntireRow.Delete

Regards,
Prasad DN
PS: If this is not resolving I may look into this by tomorrow, if still not resolved.

 

[ysherriff]

still not working...hmmm

 

[prasaddn]

Hi,

I just saw your template. I guess now i understood your situation. You need to retain row 57 where you have totals, and delete all empty rows till this.

so I suggest try this:
rownum = Range("B57").End(xlUp).Row + 1
Range("B" & rownum & ":B56").EntireRow.Delete

Regards,
Prasad DN