• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adjusting last row with total

ysherriff

Member
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
 

Attachments

  • Weekend PCF Planning - Template.xls
    59.5 KB · Views: 2
  • Weekend PCF Planning Generator v1.1.xlsm
    89.1 KB · Views: 2
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
 
it gives me a syntax error for some reason on this line

Range("B"& rownum & ":" Range("B"& rownum).End(xlDown).EntireRow.Delete
 
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.
 

Attachments

  • Weekend PCF Planning - Template.xls
    59 KB · Views: 2
  • Weekend PCF Planning Generator v1.1.xlsm
    87.1 KB · Views: 2
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.
 
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
 
Back
Top