How to use named ranges in excel vba?

Colin Legg · #1 01-25-2012, 02:46 AM

I think bosve73 finds there is a problem if someone starts inserting columns in the sheet though. Ideally this wouldn't happen and some sort of structural protection would be enforced, but using named ranges is a good safeguard. You could actually have 3 named ranges, one for each of the "Risk", "Problem" and "Status" columns.

Then your code would be something like this (I've broken it out into logical steps):

Code:

Public Sub Save_Click()
    Dim lNextRow As Long
    Dim wksDb As Worksheet
    
    Dim rngRisk As Range
    Dim rngProblem As Range
    Dim rngStatus As Range
    
    Set wksDb = Worksheets("Databas")
    
    Set rngRisk = wksDb.Range("Risk")
    Set rngProblem = wksDb.Range("Problem")
    Set rngStatus = wksDb.Range("Status")
    
    ' The statement simulates activating the last cell in the "risk" column, pressing End,
    ' pressing Up Arrow, and then moving down one row. If you do that manually,
    ' the cell pointer will be in the next empty cell in the "risk" column  — even if the data
    ' area doesn’t begin in row 1 and contains blank rows.
    lNextRow = wksDb.Cells(wksDb.Rows.Count, rngRisk.Column).End(xlUp).Offset(1, 0).Row
    
    wksDb.Cells(lNextRow, rngRisk.Column) = Me.cboRisk.Value
    wksDb.Cells(lNextRow, rngProblem.Column) = Me.txtProblem.Value
    wksDb.Cells(lNextRow, rngStatus.Column) = Me.cboStatus.Value
End Sub

bosve73 · #2 01-25-2012, 06:25 AM

Thank you Collin that was exactly what I was looking for. I tried your code and it works perfect. I still have some questions since I really want to learn more about VBA.

Code:

lNextRow = wksDb.Cells(wksDb.Rows.Count, rngRisk.Column).End(xlUp).Offset(1, 0).Row

I understand how the rest of the code works except the line above, to be more specific (wksDb.Rows.Count, rngRisk.Column).End

How come i don't need to add the rest of the ranges(problem and status)?

Colin Legg · #3 01-25-2012, 09:26 AM

That's a very good question and the answer is that the code makes a big assumption (this assumption was already included in your original post). When the VBA code determines the first free row, it does so by only checking the Risk column. It assumes that the first free row in the risk column will be the same for the others. If that may not be the case then you should change the code to check them all.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
email as pdf attachment - subject line and attachment named after mail merge	Nexus	Mail Merge	12	04-13-2011 11:34 PM
Named range drop-down	jgelpi16	Excel	1	04-08-2011 03:08 PM
Dynamic Named Ranges using text	hannu	Excel	0	06-22-2010 04:42 PM
Can't import home adresses in outlook 2010 from excel named ranges	eekie	Outlook	0	05-14-2010 02:04 PM
Opening multiple Excel files within the same Excel window.	lost9471	Excel	2	05-01-2010 01:57 PM