Excel VBA - Highlight Specific Rows

[alex_boothby]

Hi there,

I have spemt hours trying to do this to no avail.

Basically we have a water leak on site and we have a smart meter that outputs all flow data in intervals of 15mins for the past 2-3 years. So as you can imagine, 3 years split into 15mins is a lot of data!

Basically we are interested in 02:45:00 in the morning (this is time formatted in excel). I want to extract each 02:45:00 from every day. As at this time of day the flow should be close to 0! but it is not!

So I want to be select each each cell in the row that contains 02:45:00. This way I can copy the flow data that is in the same row as the time and then paste into another sheet so I can play with it (make graphs etc).

Is there a way to search and select each row that contains 02:45:00?



I have used VBA (copied someone elses) that selects every 95th row (this is the amount of cells between each 02:45:00), but this gets messed up with day lights saving times also for some reason there are some times missing on certain days so then its not every 95th row therefore messing it up for each latter 02:45:00! So it took me ages to correct for these faults and that was just for Jan-Dec this year! Would take far to long for 2013-current date.

I will kiss anyone who can help. Or wont depending on their preference...



I'm pulling my hair out!

 

[cb600fshornet]

Mhm, I'm no VBA expert, but I'm aware that as a normal excel function you can just Ctrl-F and find "all" with the search criteria, maybe that's worth looking at. Also you could run a manual filter by selecting the column you want, click filter, and then select the time you require. (assuming your time and date columns are split)

 

[alex_boothby]

Thanks.

I did try that, but when I filter by time so all the 00:2:45 are grouped it messes up the chronological order

 

[alex_boothby]

Ahhhaaaaaaaaaaaaa

I think I have the solution:

Highlight all data set up filters - on the time column untick all times and then reselect 02.45!!!

Please let me know if anyone can see any issues with this?!

Or equally if they have any expertise on finding a water leak losing a swimming pool amount of water each day!

 

[cb600fshornet]

Excellent, glad you could find a solution!

 

[setishock]

No one has seen a leak of that magnitude inside or out? 15,000 gallons is a lot of wasted water. Surely to God some one has seen that.

If it's underground, the land where the leak is, is going to a swamp by now. Ground piercing radar is going to show you where the leak is, but that's expensive. Well so is 15,000 gallons of water.

The other thing is somebody may be filling up a tanker truck on your dime. The time of night points in that direction. Cameras or a few roving guards should be able to put a stop to that if that's the case.

Why would your company let something that monumental go for so long? I'd have been all over that the first month the outrageous water bill came in.
Even with a bulk rate on water that's going to get really expensive sooner or later...