Solved

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

Posted on 2011-09-29
12
159 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 19

Expert Comment

by:akoster
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:akoster
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:
akoster 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
 
LVL 31

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:akoster
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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:akoster
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:akoster
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 31

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now