Identifying a range of data

QuestionEdit

I am working on a project and have hit a snag, and I¡¯m hoping you can help get me back on track.

The data in my spreadsheet is organized by location in column D and uses a letter-number combination as the identifier (e.g. A-1, A-2, B-1, etc.). The column is consistent and the data is always grouped by this identifier, by that I mean that the identifier will ALWAYS be in column D and all of the A-1 data will be grouped together. Each section has between 5-300 rows of data. The primary focus of the worksheet will be the data recorded by the user in columns G, H, and I ¨C this too would be constant (meaning that the user will always input the relevant data in those three columns).

My challenge is that the number of rows in each "section" will change from month to month, increasing and decreasing in size, which means I can't use a "set" range. The data will be entered by a large number of users, who have varying levels of knowledge and experience. I feel I need to use VBA to eliminate having code that can be accidentally modified/deleted by the end users.

What I need is something that will search Column D for the first instance of a location (for example ¡°A-1¡±), which would be the start point, and the last instance of ¡°A-1¡± which would be the end point, identifying the number of rows in that range.

From there the code would need to run one set of code on column G, another set of code on column H, and a third set of code on column I.

This example might better illustrate what I¡¯m trying to do:

Under Private Sub cmdA1_Click()

¡®Code searches Column D for the first cell whose value = ¡°A-1¡±

¡®Code identifies that cell as D15

¡®Code searches Column D for the last cell whose value = ¡°A-1¡±

¡®Code identifies that cell as D30

This means that there are 15 rows in section A-1.

It would then run one set of code on G15:G30, a second set of code on H15:H30, and a third set of code on I15:I30.

All three codes use a loop and nested IF functions to compare the current date, the date entered by the user in columns G, H, and I, and the equipment type (in column B) to determine whether a certain amount of time has elapsed. This in turn dictates what action(s) the code takes.

I¡¯ve already written and tested the code that will run for columns G, H, and I and it works fine. I just need help "pointing" it towards the right sections.

I've tried to research this online (checking forums, conducting searches, etc.) and have tried using named ranges, offset, countif, etc. and haven't been able to make anything work.

Hopefully this all makes sense. I'm self taught, so I'm sure that my failure to make it work is related to some gap of knowledge on my part (there are many, I'm sure). Any help on this would be greatly appreciated!!!

AnswerEdit

Sub findTopBot() 'topRow is first occurance of "A-1" in col D 'botRow is last occurance of "A-1" in col D 'identify the range in col G that runs from topRow to botRow

   Dim wks As Worksheet
   Dim topRow As Range, botRow As Range, rngColG As Range
   Set wks = ActiveSheet
   Set topRow = wks.Range("D1")
   Do Until topRow.value = "A-1"
       Set topRow = topRow.Offset(1)
   Loop
   Set botRow = wks.Cells(topRow.SpecialCells(xlCellTypeLastCell).Row, 4)
   Do Until botRow.value = "A-1"
       Set botRow = botRow.Offset(-1)
   Loop
   Set rngColG = Intersect(wks.Range(topRow, botRow).EntireRow, _
       wks.Range("G1").EntireColumn)

End Sub