Ask Experts Questions for FREE Help !
Ask
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
    		  
    #1

    Apr 18, 2011, 04:05 PM
    Dynamic Named Range
    I use a named dynamic named range in my workbook for a daily report. After I copy into a spreadsheet a bunch of data, I manipulate some of the data. Some of the rows get deleted that I don't need. The odd thing is after I delete all of the unnecessary rows and create the named dynamic range, one blank row keeps appearing in the range. I then try to delete the blank row but when I view the range in the edit mode, I still see that the blank row is included.

    Here is the formula that I am using:

    =OFFSET('Daily Summary'!$A$2,0,0,COUNTA('Daily Summary'!$A:$A),COUNTA('Daily Summary'!$1:$1))

    Can I add another layer of code to ignore a blank row?

    Thanks.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Apr 19, 2011, 01:08 AM

    Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

    Post up the workbook and let's take a look.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #3

    Apr 19, 2011, 01:22 AM

    Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

    Post up the workbook and let's take a look. I use many dynamic named range techniques, I'm not sure I've ever used the one you've presented.
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
    		  
    #4

    Apr 19, 2011, 08:25 AM
    Quote Originally Posted by JBeaucaire View Post
    Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

    Post up the workbook and let's take a look. I use many dynamic named range techniques, I'm not sure I've ever used the one you've presented.
    Jerry - here's a modified copy of the workbook... just one sheet in this example.

    Incidentally, AMHD won't allow .xlsx docs to be attached so I had to save this as Excel 2003 with some loss of fidelity.

    Thanks.
    Attached Files
  1. File Type: xls book1.xls (49.0 KB, 122 views)
    2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #5

    Apr 19, 2011, 02:15 PM

    I'll take a look. If I'm not mistaken you can attach any file type by zipping it.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #6

    Apr 19, 2011, 02:19 PM

    Well, I hate to say is seems obvious, but you do know what COUNTA() does, don't you?

    COUNTA($A:$A) will give you a count of every cell in column A that has a value in it, including row 1 where your titles are. Since you obviously want the OFFSET() to skip row 1, you will need to subtract 1 from the count of column A.

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search


Check out some similar questions!

Question about Dynamic Range for Pivot Tables - Excel 2007 [ 1 Answers ]

I have tried writing a module that will create a pivot table in a named worksheet with a range of data that changes from day to day. I was able to do with easily in Excel 2003 but when I tried to duplicate that in Excel 2007, it doesn't work. It fails with a Run-time error 5: Invalid procedure...

For High Dynamic Range photography: D80 or D200? [ 1 Answers ]

I am looking to do High Dynamic Range photography and am aware of the specs of the 2 camera models among which I am considering for purchase: The Nikon D80 does 2 to 3 exposures in 1/3 to 2 EV increments. The Nikon D200 does 2 to 9 exposures in 1, 2, or 3 EV increments This having been said,...

Name of movie: Killer named ghost or the ghost; man named king who escaped by falling [ 1 Answers ]

Killer named ghost or the ghost; man named king who escaped by falling out of a window; man who ghost thinks of as worthy of being his predecessor

Changing from a gas range to an electric smooth top range [ 1 Answers ]

What are the requirement for switching from cooking with a gas stove to cooking with and electric stove

View more questions Search