Delete certain rows and highlight certain cells

[James Niven]

Hi there,
So, I have created a logging template for my listening hobby.
I would like to create a macro that will do the following to each block on each tab.

I would like enter in the UTC time and then delete the other rows that do not equal the UTC time entered, but leave a row above and below the entered UTC time. The UTC times are listed down column A.

On the tab 530-590 the cell with 530 at A5 is the frequency, numbers on row 6 across the screen are the minutes.

Next, I would like to entry in the minutes when I will start listening on this listening session.
So, if the minutes are 58-02, then I would like that range to be shaded in yellow on each frequency block and each tab.

So, for example if I was listening at 0458utc and ending at 0502utc, all the other rows would be deleted except the 0400 and 0600 and 58-59 would be highlighted in yellow as well as 00-01-02 would be highlighted in yellow. Hope this makes sense.
I have included a demo tab on my attachment to what I am looking for.

I know some VBA, but unsure where to start on this.
Thank you in advance for any help offered.

[James Niven]

I did try to start this project with what VBA knowledge I know, but got stuck.



Sub DeleteRows()

'Declare Variables
Dim ws As Worksheet
Dim myutc As Range
Dim sutc As String
Dim eutc As String
Dim sminutes As String
Dim eminutes As String

'Setup variables
ThisWorkbook.Worksheets("530-590").Range("B1").Value = "Test"
Set myutc = Range("A7:A21")

'Ask for UTC Time
sutc = InputBox("Enter in Start UTC", "SDR Logging Sheet")
eutc = InputBox("Enter in End UTC", "SDR Logging Sheet")

'Ask for Minutes
sminutes = InputBox("Enter in Start Minutes", "SDR Logging Sheet")
eminutes = InputBox("Enter in End Minutes", "SDR Logging Sheet")
Debug.Print sutc
Debug.Print eutc
Debug.Print sminutes
Debug.Print eminutes

'Delete unwanted rows
With myutc.Delete
.EntireRow
End With

'Highlight minute cells in yellow
'Code Cleanup
MsgBox ("Completed")

End Sub


Any help would be very helpful!
Thanks

[Zack Barresse]

James,

Since you have multiple inputs, I would recommend a userform here. That way you can validate the inputs all at once and the user experience is much better. Have you thought about filtering out values instead of deleting rows? Or perhaps creating a new sheet based on a template? Pertaining to the specifics of your data, if you list a time as 58-02, wouldn't that go to the next hour, so you'd want to keep two rows plus the one above and below them, with a total of 4 rows?

[James Niven]

Hi Zack Barresse,
Thanks for the reply. Yes, I did think of a Userform while I was trying tosolve this problem myself. As I have mentioned my VBA skills are not that good,so any help you can offer would be helpful.
Yes, 58-02 will go to the next hour, this is correct, soafter a little thought I would be happy with two rows of the wanted hours,rather than the 4 I initially thought about.
Thanks

[Zack Barresse]

What do the sheets represent? What are the bold numbers going down column A?

[James Niven]

Zack,

The bold numbers under column A represent the tuned frequency on the AM radio band as shown on your car radio.

[James Niven]

Also, the sheets represent the block of frequencies. The AM radio band covers 530 through to 1700 in 10khz steps, so about 120 frequencies or so

[James Niven]

anyone willing to tackle my little project??