Set first rows values in certain column H to 0.

[u27sc8]

sub test()
dim i as long
i=activecell.row
range(cells(i,"C"),cells(i,"D"),cells(i,"E")).clearcontents
end sub

Hello,

This is my first time here. I have just worked out how to run a macros in excel, and set up a button in the ribbon.

Next, the aim is to create VBA which will change every cell value, in the first row, in collumn H, for all rows in the sheet, to zero.

The above code is as close as I can find on google. The problem is I do not know how to set the range to the entire sheets first rows only. (I have VBA which inserts blank row between every value change in collumn D).

Best regards

[Kevin#]

Your request is a bit confusing. But this should help you understand

Please Login or Register  to view this content.

[u27sc8]

Hi Kevin,

Thanks for taking the time to reply.

Every week new data is imported into the excel sheet.

It is for tracking the amount of fuel each vehicle uses.

I have code that seperates different registrations with a blank row.

Then I am trying to get the first fuel entry in every first row to be changed from a number to 0.

None of the code above shows an action for every first row, though.

Does that make sense?

Your probably wondering why I need to replace fuel numbers with 0, so ill give an example which shows the same truck over 3 days:

truck 1 06/03/2016 100.6 litres 100500 miles
truck 1 07/03/2016 250.2 litres 100700 miles
truck 1 08/03/2016 175.4 litres 100900 miles

to get the true miles per gallon I need the below: (note the first fuel value is now 0)

truck 1 06/03/2016 000.0 litres 100500 miles
truck 1 07/03/2016 250.2 litres 100700 miles
truck 1 08/03/2016 175.4 litres 100900 miles

mpg = 000.0
250.2
175.4
---------
425.6 litres total

100900-100500 = 400 miles

So 400/ 425.6 = 0.93 miles per gallon.



The final point to explain is that all vehicles tanks are filled at the ends of each run, so the fuel that is put in relates to the previous fuel burned, and so the first fuel value is changed to 0 because there are no previous mileages to link that amount of litres to.

I really hope I have explained this, as I know it is confusing as it took me ages to work out why the fleet mpg was better than it actually was in reality!

[Kevin#]

I understand what you are trying to do, but cannot visualize your sheet layout
Please reply attaching your workbook with 2 sheets.
- Sheet 1 is what it looks like before doing what you want
- Sheet 2 is how you want it to look after doing what you want

thanks