Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Named Range VBA

Posted on 2012-08-22
11
Medium Priority
?
519 Views
Last Modified: 2012-08-24
Hello All,

I have a named range VBA question
Is it possible via VBA to go across a specific row (row  = 4) and find the leftmost and rightmost cells and then create name range “myRng” with those cells – I am looking for a VBA way of doing this…

Thank you
namedRangeCreateHow.xlsx
0
Comment
Question by:Rayne
11 Comments
 

Author Comment

by:Rayne
ID: 38323542
so for cell range C1: C4, create the named range via VBA
But first locate the leftmost and rightmost cells in row 4
0
 

Author Comment

by:Rayne
ID: 38323584
I did this but still getting error
0
 

Author Comment

by:Rayne
ID: 38323585
Sub Button1_Click()

Dim fixedHeaderRow As Long


Dim f1, f2 As String

f1 = FirstColumn(ActiveSheet)
f2 = LastColumn(ActiveSheet)

'ActiveWorkbook.Names.Add Name:="myRng", RefersTo:="Sheet1!A2:A" & Range("A2").End(xlDown).Row

ActiveWorkbook.Names.Add Name:="myRng", RefersTo:="Sheet1!" & f1 & ":" f2




End Sub


Private Function FirstColumn(TheWorksheet As Worksheet) As String

If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    FirstColumn = Replace((Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Address), "$", "")
End If

End Function



Private Function LastColumn(TheWorksheet As Worksheet) As String
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    LastColumn = Replace((Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Address), "$", "")
End If
End Function

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rayne
ID: 38323586
I am not still getting it, please assist
0
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 1200 total points
ID: 38323601
Here's how I would do it:

Option Explicit

Sub FindAndName()
    
    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    Dim leftStr As String
    Dim rightstr As String
    Dim leftRng As Range
    Dim rightRng As Range
    Dim rangeToName As Range
    Dim myRng As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    
    leftStr = "C1"
    rightstr = "C4"
    
    On Error GoTo Errhandler:
    Set leftRng = ws.UsedRange.Find(What:=leftStr, LookIn:=xlValues, SearchOrder:=xlByRows, Searchdirection:=xlPrevious)
    Set rightRng = ws.UsedRange.Find(What:=rightstr, LookIn:=xlValues, SearchOrder:=xlByRows, Searchdirection:=xlPrevious)
    Set rangeToName = ws.Range(leftRng, rightRng)
    
    On Error Resume Next
    Set myRng = ws.Range("myrng")
    On Error GoTo 0
    
    If myRng Is Nothing Then
        ws.Names.Add Name:="myrng", RefersTo:=rangeToName
    Else
        wb.Names("myrng").Delete
        ws.Names.Add Name:="myrng", RefersTo:=rangeToName
    End If
    
    Exit Sub
Errhandler:
MsgBox ("Headings not found.")
End Sub

Open in new window

0
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 400 total points
ID: 38323608
Hi Rayne,

Give this a shot...
Public Function setNamedRangeInRow4()

    'WRITTEN BY:    AEBEA
    'WRITTEN ON:    2012.08.22
    'FOR QUESTION:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27839201.html
    
    Dim currentCellColumnIndex As Integer
    Dim leftMostColumn As Integer
    Dim rightMostColumn As Integer
    
    'GET THE RANGE
    For Each cell In Range("4:4")
        If Len(cell) > 0 Then
            'get the column of the cell
            currentCellColumnIndex = cell.Column
            If leftMostColumn = 0 Then
                leftMostColumn = currentCellColumnIndex
            ElseIf currentCellColumnIndex < leftMostColumn Then
                leftMostColumn = currentCellColumnIndex
            ElseIf currentCellColumnIndex > rightMostColumn Then
                rightMostColumn = currentCellColumnIndex
            End If
        Else
        
        End If
    Next
    Debug.Print "LEFTMOST CELL COLUMN: " & leftMostColumn
    Debug.Print "RIGHTMOST CELL COLUMN: " & rightMostColumn
    'CREATE THE NAMED RANGE
    ActiveWorkbook.Names.Add Name:="this_named_range", RefersToR1C1:= _
        "=Sheet1!R4C" & leftMostColumn & ":R4C" & rightMostColumn
End Function

Open in new window

0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 total points
ID: 38323631
Here is my take

Sub getcolhdrs()
dim fc as range, lc as range, hdrrng as range
Set fc = ActiveSheet.Range("4:4").Find("*", Cells(4, Columns.Count), , , , xlNext)
Set lc = ActiveSheet.Range("4:4").Find("*", Cells(4, 1), , , , xlPrevious)
Set hdrrng = Range(fc.Address, lc.Address)
End Sub
0
 

Author Comment

by:Rayne
ID: 38325525
Hello All,

Thank you for the deferent approaches. :)

techfanatic ‘s code was complete except for he  assumed the left and right cells are C1:C4. On the other hand,, Ssaqib showed the quickest way to locate the left and right cells
 while Aebea iterated through all the cells in the header row. Good to know all the different approaches.

Thanks guys for the platinum help.
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38325535
Edit: Nevermind, I misread the post.
0
 

Author Comment

by:Rayne
ID: 38330280
A relevant follow up posted here if you are interested
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27841478.html

Thank you
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question