Solved

How to select and name a dynamic range of cells in a column

Posted on 2011-09-29
12
171 Views
Last Modified: 2012-05-12
I ran the macro recorder to capture some code.

How do I edit this to do this same thing without having the absolute cell references in it?

I would like to combine the two subs into one.   -R-
Sub PrepCNLT_ForVLOOKUP()
'
' PrepCNLT_ForVLOOKUP Macro

    Sheets("FormatCNLT").Select
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Run "'Daily Metrics.xlsm'!ConcateniateColAandColB"
    Columns("C:C").EntireColumn.AutoFit
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Yesterday,1,FALSE)"
    Range("G2").Select
    
' Need to add something here to autofill formula down no matter how many rows
' are in that column

    Selection.AutoFill Destination:=Range("G2:G418")
    Range("G2:G418").Select
' Without using absolute cell references.
    
    
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select
End Sub

Sub NameTheNewlyConcatinatedRangeToYesterday()
'
    Range("C2").Select
    
'Same thing here, I need to select however many rows in the column, and name the
'range yesterday.  It would be nice if this could get included into the above Sub

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="Yesterday", RefersToR1C1:= _
        "=FormatCNLY!R2C3:R412C3"
End Sub

Open in new window

0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36815207
The destination value is a required attribute for the autofill function and thus must be used.
You could use something like

lastrow = Range("B2").End(xlDown).Row
Range("C2").AutoFill Range("C2:C" & lastrow)

Open in new window

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36815216
sorry, that would be
lastrow = Range("C2").End(xlDown).Row
Range("G2").AutoFill Range("G2:G" & lastrow)

Open in new window

0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 500 total points
ID: 36815268
sub then would be
Sub PrepCNLT_ForVLOOKUP()
'
' PrepCNLT_ForVLOOKUP Macro

    Sheets("FormatCNLT").Select
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Run "'Daily Metrics.xlsm'!ConcateniateColAandColB"
    Columns("C:C").EntireColumn.AutoFit
    Range("G2").FormulaR1C1 = "=VLOOKUP(RC[-4],Yesterday,1,FALSE)"
    
    lastrow = Range("C2").End(xlDown).Row
    Range("G2").AutoFill Range("G2:G" & lastrow)

    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

     '-- name the concatinated range to yesterday
    ActiveWorkbook.Names.Add Name:="Yesterday", RefersToR1C1:= "=FormatCNLY!R2C3:R" & lastrow & "C3"
End Sub

Open in new window

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 36815273
You could create a dynamic named range for columns C & G based on the number of entries in another column.

In the define name window, create a Name for column C and use the following in the Refers To input:

=OFFSET(FormatCNLT!$C$2,0,0,COUNTA(FormatCNLT!$B:$B),1)

Repeat for column G with:

=OFFSET(FormatCNLT!$G$2,0,0,COUNTA(FormatCNLT!$B:$B),1)

This assumes the required number of rows can be counted in column B. If this needs adjusting add a +/- amount after the COUNTA(...) section.

You can then refer to the named ranges in your VBA.

Thanks
Rob H
0
 

Author Comment

by:RWayneH
ID: 36815645
Sorry I wanted to get the concept here.  I believe I did, however in the final code with edits, it did not seem to define "CNL_Today" for me?

Could you review this code... and see where I am missing something?  especially where the CNL_Today add name defination was missed?

I think that I am pretty close. -R-
Sub ResetTheCNL_SheetTabs()
'
' ResetTheCNL_SheetTabs Macro
'

'may need on error, next here or Display Error false

    ActiveWorkbook.Names("CNL_Today").Delete
    ActiveWorkbook.Names("CNL_Yesterday").Delete
    
    Sheets("FormatCNLY").Select
'Rename column C to Yesterday, instead of Today.
    Range("C2").Select
    
    lastrow = Range("C2").End(xlDown).Row
    ActiveWorkbook.Names.Add Name:="CNL_Yesterday", RefersToR1C1:="=FormatCNLY!R2C3:R" & lastrow & "C3"
    
'    Range(Selection, Selection.End(xlDown)).Select
'    ActiveWorkbook.Names.Add Name:="CNL_Yesterday", RefersToR1C1:= _
'        "=FormatCNLY!R2C3:R412C3"
    
'gets rid of old VLOOKUP definations.
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    
    Sheets("FormatCNLT").Select
'insert new column in
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Run "'Daily Metrics.xlsm'!ConcateniateColAandColB"
    Columns("C:C").EntireColumn.AutoFit
'insert the CNL_Today name.

    Range("C2").Select
    
    lastrow = Range("C2").End(xlDown).Row
    ActiveWorkbook.Names.Add Name:="CNL_Today", RefersToR1C1:="=FormatCNLT!R2C3:R" & lastrow & "C3"
    
'    Range(Selection, Selection.End(xlDown)).Select
'    ActiveWorkbook.Names.Add Name:="CNL_Today", RefersToR1C1:= _
'        "=FormatCNLT!R2C3:R418C3"
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],CNL_Yesterday,1,FALSE)"
    
'autofill formula down, need to remove absolute references in this next line.
   
    lastrow = Range("G2").End(xlDown).Row
    Range("G2").AutoFill Range("G2:G" & lastrow)

'    Selection.AutoFill Destination:=Range("G2:G418")
'    Range("G2:G418").Select
    
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "New?"
    Range("G2").Select
    
    Sheets("FormatCNLY").Select
    Range("G2").Select
'    Range("G2").FormulaR1C1 = "=VLOOKUP(RC[-4],CNL_Yesterday,1,FALSE)"
    
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],CNL_Today,1,FALSE)"
    
    lastrow = Range("G2").End(xlDown).Row
    Range("G2").AutoFill Range("G2:G" & lastrow)

'autofill formula down, need to remove absolute references in this next line.

'    Selection.AutoFill Destination:=Range("G2:G412")
'    Range("G2:G412").Select
    
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "New?"
'Remove formulas, paste special values
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("G:G").EntireColumn.AutoFit
    Application.CutCopyMode = False
'remove formulas, paste special values.

    Sheets("FormatCNLT").Select
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("G:G").EntireColumn.AutoFit
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    
'*****  Need a with filter statement here.
    .AutoFilter
        .AutoFilter Field:=7, Criteria1:="#N/A"
        
    End With
'    ActiveSheet.Range("$A$1:$L$418").AutoFilter Field:=7, Criteria1:="#N/A"
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36815760
I see nothing wrong, lines 15 and 16 should define the name CNL_Yesterday.
lines 36 and 37 should define the CNL_Today name.

even when the FormatCNLT worksheet does not exist, the name should be generated.
can you post the workbook ?
0
 

Author Comment

by:RWayneH
ID: 36815883
It was my.. sorry.  I know this is not part of the original ?

But why would the final step fail?  When I try and setup the filter by #N/A  ?   -R-
'Setup autofilter on sheet.
    Sheets("FormatCNLT").Select
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    
Dim rgFilter As Range
    With rgFilter
'        .AutoFilter
    .AutoFilter Field:=7, Criteria1:="#N/A"
    End With
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36891146
That would most likely be caused by the empty range rgFilter.
you did not supply the range for which the filter is going to be set.

you can do this by adding this code in between lines 7 & 8

set rgFilter = range("A1:X12")

Open in new window

update the actual range to your liking
0
 

Author Comment

by:RWayneH
ID: 36892843
Ok. My header labels are in row 1, how do I tell it to select whatever the range is that is being autofiltered.  I can not use absolute cell references.  

set rgFilter = range(" ?data? ")

Not sure how to tell it to use the range that excel selects when you turn on autofilter?  -R-
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36901958
you could use

set fgFilter = range("data").

in order for this to work however, you will need to tell excel which cells correspond to the named range data. You can do this by selecting an arbitrarily range of cells, and type in the name (data) in the field just above the top-left cell (which should contain the name of the top-left selected cell).
Using menu's this would be : select some cells, click the Formulas menu and locate the Named Cells panel in Excel 2007. In Excel 2010, locate the Defined Names panel instead.
Click Name a Range in Excel 2007 and Define Name in Excel 2010

If you turn on autofilter in excel, the whole column is filtered. You can also do this using macro code by selecting a complete column (select by clicking on the column name instead of separate cells), or by using a more absolute reference :

set rgFilter = range("B:D")

thereby filtering on columns B, C and D
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36905100
You could select current region before activating the auto filter. This should select all contiguous data from the cell which is active when current region selection is made. Ctrl & asterisk to select current region.
0
 

Author Closing Comment

by:RWayneH
ID: 37073094
Thanks for the help.  -R-
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

724 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