Find all lists in a workbook.

[Darren Bartrup]

It's been a while since I've visited MrExcel, but I've been busy learning C# & ASP.Net & Visual Studio & SQL Server - work doesn't want too much from me

Anyway, I'm writing a procedure that plays around with lists in Excel (i.e. the type of list that would show up in CurrentRegion.
They're not connected to any outside data source, or as a pivot table - just columns of data that will have a header row in a different format than the data body.

Is there any way of cycling through each sheet in the workbook and identifying each occurrence of a list?

I've been playing with the code at the bottom of the post, and it finds the boundaries of each list providing (at the moment) that each list starts on row 1. There are other problems with it however.

Basically, is there an easier way to go about this? And to identify the values in the header row?

Many thanks in advance for any help.

Darren.

For Each wrkSht In ThisWorkbook.Worksheets
    Set srchCell = wrkSht.Cells(1, 1)
    Debug.Print "Worksheet: " & wrkSht.Name
    Do Until srchCell.Column > 255
        rTble = srchCell.CurrentRegion
        If Not IsEmpty(rTble) Then
            Debug.Print "Start Row: " & srchCell.Row & " ~ End Row: " & UBound(rTble)
            Debug.Print "Start Col: " & srchCell.Column & " ~ End Col: " & srchCell.Column + UBound(rTble, 2)
            Debug.Print
        End If
        Set srchCell = srchCell.End(xlToRight)
        If IsEmpty(srchCell) Then
            Set srchCell = srchCell.End(xlToRight)
        End If
    Loop
Next wrkSht

 

[Zack Barresse]

Hi there,

Are you saying you want to cycle through each contiguous range of data on the sheet, and in each sheet of a specific workbook? Can you give a couple quick examples of data ranges? I.e. A1:B2, C5:D7, G12:J20, etc. I'm assuming they can be disjointed? Not all start on row 1 or have headers? None of the ranges will be in the same current region, and if they are they will be included in said current region?

 

[Darren Bartrup]

Thanks for reply firefytr.

Those ranges you gave would be a perfect example. Each list doesn't have to start on row 1, but they should all have a header row and each list will be in a region of it's own.

Basically, if a user enters into a form a row header - e.g. "DOB", or "User Name" then the code will search the workbook for the list containing that header. Obviously there may be more than one list with that header, but if I can find the lists then I can give the user the option of which list to choose from.

So from that I should say that a list is a continous range of cells anywhere in the workbook that is made up of more than one column and more than one row, which has a header formatted differently from the body - and each list will be in a region of it's own.

I hope that explains it.

Regards,
Darren.

 

[Darren Bartrup]

Looking back now... sorry, was around a friends and in a hurry when I replied first. When I say region - I mean each list will be seperated by at least a single empty column and/or row.

Would these be in the same region? I'm not sure.

 

[Norie]

Darren

What will be in these 'lists'?

By the way are you sure this is the best data structure - sounds like you'll have data all over the place.

Or am I missing something?

 

[Darren Bartrup]

Hi Norie,

I'm trying to write a reusable list finder I guess, so the lists could contain pretty much anything - dates, text, numbers in any column order.

The idea is that from code a procedure will be called that will ask for a unique ID, an action to be performed on the match and a range of some sort which the unique ID will appear in.

As I want the code to be reusable in the future (not necessarily to be reused in the same project) I want the future use to be able to accept as a search range anything from a range of cells (e.g. A2:B36), a named range or a field heading.
So if a field heading is entered the code needs to be able to search the workbook for that heading and identify the list that it appears in as the action will be performed on the matching record in the list - either deleting a row, inserting a row or updating values within the row.

So yes, the data structure is very bad - in that the list could be anywhere and the list could consist of any type of data but it will contain a column that contains data in the same structure as the unique identifier (although it may not contain the unique identifer exactly - that's where the adding a row comes in).

Very complicated, but I'm sure is possible if I can identify where the lists are.

Below is my full code so far.
Calling the "Amend" procedure with the following arguments will identify the SearchRange as a named range in my test worksheet, but it also needs to check to see if it's the heading in a list so it can give the option of which to use.

Amend Sheet1.Cells(13, 1), Delete, "Heading_1"

Option Explicit

Public Enum Action_Type
    Insert = 1
    Overwrite = 2
    Delete = 3
    UpdateORInsert = 4
End Enum

Private Enum SearchType
    srchWorkbook = 0
    srchWorkSheet = 1
    srchRange = 2
    srchWorkSheetRange = 3
    srchNamedRange = 4
    srchField = 5
End Enum
    
    

Public Sub Test()

'Amend 1234567890.2345, Delete, Sheet1.Range("A2:A36")
'Amend Sheet1.Cells(13, 2), Delete, ""
'Amend Sheet1.Cells(13, 1), Delete, "A2:A36"
Amend Sheet1.Cells(13, 1), Delete, "Heading_1"


End Sub

Public Sub Amend(SwiftID As Variant, Action As Action_Type, SearchRange As Variant)

Dim lSearchRngType      As Long
Dim NamedRangeSrch      As Name
Dim lSearchType         As Long
Dim wrkBk               As Workbook
Dim wrkSht              As Worksheet
Dim rTble               As Variant
Dim srchCell            As Range

If VarType(SearchRange) = vbString Then
    If Not (SearchRange = "") Then
        '//Is SearchRange a named range?
        For Each NamedRangeSrch In ThisWorkbook.Names
            If (SearchRange = NamedRangeSrch.Name) Then
                lSearchType = SearchType.srchNamedRange
                Exit For
            End If
        Next NamedRangeSrch
        '//Is SearchRange a named field?
        For Each wrkSht In ThisWorkbook.Worksheets
            Set srchCell = wrkSht.Cells(1, 1)
            Debug.Print "Worksheet: " & wrkSht.Name
            Do Until srchCell.Column > 255
                rTble = srchCell.CurrentRegion
                If Not IsEmpty(rTble) Then
                Debug.Print "Start Row: " & srchCell.Row & " ~ End Row: " & UBound(rTble)
                Debug.Print "Start Col: " & srchCell.Column & " ~ End Col: " & srchCell.Column + UBound(rTble, 2)
                Debug.Print
                End If
                Set srchCell = srchCell.End(xlToRight)
                If IsEmpty(srchCell) Then
                    Set srchCell = srchCell.End(xlToRight)
                End If
            Loop
        Next wrkSht
    End If
'//Is SearchRange a specified worksheet range?
ElseIf VarType(SearchRange) = 8204 Then

End If

End Sub

If it's not possible to find a list just from how it's structured then I'll have to do a quick fix for this project and then update the code for future projects which is a pain.

Regards,
Darren.

 

[Zack Barresse]

Well, this will loop through each region in a worksheet (and loop through each worksheet)....

Sub LoopThroughRegions()
    Dim ws As Worksheet, rRegion As Range, iCnt As Long
    Dim sAddys As String, arrAddys() As String, i As Long
    For Each ws In ThisWorkbook.Worksheets
        sAddys = vbNullString
        On Error Resume Next
        sAddys = ws.Cells.SpecialCells(xlCellTypeConstants, 23).Address(0, 0)
        If sAddys = vbNullString Then GoTo SkipWs
        If InStr(1, sAddys, ",") = 0 Then
            ReDim arrAddys(1): arrAddys(1) = sAddys
        Else
            iCnt = Len(Replace(sAddys, ",", "")) - Len(sAddys)
            ReDim arrAddys(1 To iCnt): arrAddys = Split(sAddys, ",")
        End If
        For i = LBound(arrAddys) To UBound(arrAddys)
            MsgBox "Worksheet: " & ws.Name & vbNewLine & "Range: " & arrAddys(i), vbInformation, "INFO"
        Next i
SkipWs:
    Next ws
End Sub

Is that what you're looking for?

 

[Darren Bartrup]

That looks like it could be doing it!

Just to clarify a couple of things though - if I have a range in, for example, A1:J36 the procedure will always return B1:J1 and A1:A36?
i.e. the first row (the headers) except for the first cell and the entire first column.

If that's right then THANKYOU!!!!

 

[Zack Barresse]

Alas, no. Without a break, it is seen as contiguous data and the range returned would then be A1:J36. If you had a blank column/row in between, as you stated earlier, that would then work.

 

[Darren Bartrup]

I think I must have my data set up incorrectly or something.

I didn't mean that's what I wanted returned - that IS what's being returned.
Ideally I'd like it to return A1:J36.

My test sheet is set up like this:
A1:J36 is list 1 and M1:V44 is list 2.
A1:J1 is formatted differently from the body in list 1 and M1:V1 is formatted differently to.

sAddys returns:
B1:J1,N1:V1,A1:A36,M1:M44

I can extrapolate the individual lists from this, but if you were expecting it to return A1:J36 and M1:V44 then maybe I'm doing something wrong?

Regards,
Darren.