Finding Unnamed Columns

[lmcwrod]

Hope somebody can help with the following:

I have a large database (15000 rows, 85 columns). I was in the habit of naming all columns. However, I had noticed that when I would zoom out, I could see all column names (and thus, all columns that had not been named). As such, I stopped naming the columns and was going to name them at a later date. Unfortunately, now that I've added so many columns, I no longer see the column names when I zoom out.

Is there any way for me to see which columns in the database do not have names so that I can name them and refer to the name as opposed to the column letters?

I hope that's clear.

 

[Oaktree]

If you start in A1 and press CTRL + Right arrow, Excel will take your cursor to the first empty cell in row 1 (technically, it will take you to the cell directly left of the first empty cell in row 1). After you fill in that cell, press CTRL+Right arrow again to go to the next one.

Is that sufficient for what you're looking to do?

 

[lmcwrod]

Oaktree, I'm having difficulty understanding how this solves the problem. I'm looking for a way to find which columns don't have a name associated with them (entire column, from A1-A15130; I have a total of 80 columns) without having to go column by column. The zoom out thing that I was able to do when the database was smaller was great. I could instantly identify which columns had no name and then proceed to name them.

Hope that helps clarify.

 

[Oaktree]

Are you talking about named ranges? I took "names" to refer to the cell in row 1 of each column, and I thought your issue was that some of them were blank and you wanted to fill in the values. CTRL+Right arrow would find a blank in your row...

 

[lmcwrod]

Sorry for the confusion. I am indeed talking about named ranges (each column is a named range). I'm looking to find a way of locating all columns that are not named ranges so that I can name them and refer to them by name (so that if I add columns, my references stay intact).

 

[Zack Barresse]

You might be able to try...

Sub CheckColumnsForNamedRangesPlease()
    Dim ws As Worksheet, nm As Name, LastCol As Long, i As Long
    Dim Msg As VbMsgBoxResult, blnHasIt As Boolean, strColLet As String
    Set ws = Sheets("Sheet1")
    LastCol = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByColumns, xlPrevious).Column
    For i = 1 To LastCol
        blnHasIt = False
        For Each nm In Workbooks(ws.Parent.Name).Names
            Debug.Print nm.Name & ": " & nm.RefersTo
            If Left(nm.RefersTo, Len(ws.Name) + 1) = "=" & ws.Name Then
                If Not Intersect(ws.Range(nm.RefersTo), ws.Columns(i)) Is Nothing Then
                    blnHasIt = True
                    Exit For
                End If
            End If
        Next nm
        If blnHasIt = False Then
            strColLet = CStr(ws.Cells(1, i).Address(0, 0))
            strColLet = Left(strColLet, Len(strColLet) - 1)
            Msg = MsgBox("Column " & strColLet & " has no named range.  Go there now?", vbYesNo)
            If Msg = vbYes Then
                ws.Columns(i).Select
                Exit For
            End If
        End If
    Next i
End Sub

 

[lmcwrod]

Thanks firefytr, my vba skills aren't that great but I will try the code and see if it works. It beats going column by column!

 

[Zack Barresse]

Works for me. Now, this will skip over any column if there is ANY intersect of any named range. If there should be a specific named range for a specific range of cells, you'll need to specify that and we can adapt the code to reflect such.