Go Back   Computer Forums > General Computing > Programming
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 05-21-2015, 07:02 AM   #1
In Runtime
 
alex_boothby's Avatar
 
Join Date: Apr 2005
Posts: 310
Send a message via MSN to alex_boothby
Default Excel VBA - Highlight Specific Rows

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!
__________________

alex_boothby is offline   Reply With Quote
Old 05-21-2015, 07:15 AM   #2
Fully Optimized
 
joedaman633's Avatar
 
Join Date: Apr 2012
Location: England, Birmingham
Posts: 1,812
Default Re: Excel VBA - Highlight Specific Rows

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)
__________________

__________________
Athlon II x4 645 || 1TB 7200rpm HDD || EVGA GTX 650Ti OC || 8GB DDR3 RAM || Windows 7 Home x64

i5 4210M || 500GB Samsung EVO 850 SSD || GeForce 825M || 16GB DDR3 RAM || Windows 10 x64
joedaman633 is offline   Reply With Quote
Old 05-21-2015, 07:22 AM   #3
In Runtime
 
alex_boothby's Avatar
 
Join Date: Apr 2005
Posts: 310
Send a message via MSN to alex_boothby
Default Re: Excel VBA - Highlight Specific Rows

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 is offline   Reply With Quote
Old 05-21-2015, 07:32 AM   #4
In Runtime
 
alex_boothby's Avatar
 
Join Date: Apr 2005
Posts: 310
Send a message via MSN to alex_boothby
Default Re: Excel VBA - Highlight Specific Rows

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! :P
alex_boothby is offline   Reply With Quote
Old 05-21-2015, 07:57 AM   #5
Fully Optimized
 
joedaman633's Avatar
 
Join Date: Apr 2012
Location: England, Birmingham
Posts: 1,812
Default Re: Excel VBA - Highlight Specific Rows

Excellent, glad you could find a solution!
__________________
Athlon II x4 645 || 1TB 7200rpm HDD || EVGA GTX 650Ti OC || 8GB DDR3 RAM || Windows 7 Home x64

i5 4210M || 500GB Samsung EVO 850 SSD || GeForce 825M || 16GB DDR3 RAM || Windows 10 x64
joedaman633 is offline   Reply With Quote
Old 05-21-2015, 11:00 AM   #6
Wizard of Wires
 
setishock's Avatar
 
Join Date: Feb 2005
Location: Not sure
Posts: 10,030
Default Re: Excel VBA - Highlight Specific Rows

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...
__________________

setishock is offline   Reply With Quote
Reply

Tags
excel, nth, row, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 07:17 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0