go to bottom of named range and add an item,

if the named range is monthof and contains Jan, Feb, Mar,

i would like to go to the bottom row of monthof and add Apr...(now having 4 rows in the range)
(i'm using the function: =OFFSET($M$2,0,0,COUNTA($M:$M),1) so as to have a dynamic range name)


???

Using VBA?

Since your objective is to simply add a new item of data on the next available (un-used) row in column M, the vba you can use to accomplish this..Doesn't actually have to even pertain to your named range.



This post has been edited by ipisors: May 30 2012, 04:35 PM

yes.

thank you.

(IMG:style_emoticons/default/yw.gif)

thank you...

works well, but sometimes is goes a few below the end and adds ALL after a few blank cells...?

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("lookup")

Range("e1").Select
lastrow = ws.Range("e" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
ws.Range("e" & lastrow - 2).value = "ALL"

Range("g1").Select
lastrow = ws.Range("g" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
ws.Range("g" & lastrow).value = "ALL"

Range("k1").Select
lastrow = ws.Range("k" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
ws.Range("k" & lastrow).value = "ALL"

??? (blank cells at bottom of range...)

thank you...

The method I used does the same thing as if you went to the bottom row in the workbook (like the 1 millionth row or whatever), and hit keyboard: Ctrl+UpArrow, and then went one row down from there. This "end(xlup)" type of method may sometimes not suit you, because it may 'catch' on a cell that appears blank, but has a formula in it. Or otherwise is dirty.

The rows that it wouldn't skip over (the 'blank' ones), are you sure they're 100% blank? free of values, formulas, spaces, dots, color, borders, etc?

This post has been edited by ipisors: May 30 2012, 05:54 PM

well....(there's always more to the story...)

these ranges are linked to an Access query and this code seems to also ruin the connections (i get a corrupted excel and when i say Yes, the xls comes back but the range links are broken...

well...wdyt?

thank you.

Not sure how it would ruin the connection, except maybe if it places a value in a cell that's inside a query table / query range. But yeah - I can see that cell being seen as dirty. Which would make the code skip over it (at worst), though...not place a value in it.

thanks...

we may abandon this idea...(at least for the moment...)

thank you.

Let me see if I understand what you are doing. You have a query that returns data to the excel spreadhseet and you have assigned a name to this dynamic range and you want to add a new line at the end of the range.

My first question is, since you are using Excel 2010, doesn't the query return the data to an Excel Table?

If so, Range("TableName[#All]").Rows.Count will give you the number of rows in the table (inlcluding the header). This result gives you the last used row in the table. It doesn;t matter what is below it.

Anything added ot the next row automatically becomes part of the table. In other words, a table is its own dynamic range with the added benefit of automatically duplicating formulas and formats.

You could also use Range("RangeName").Rows.Count if you don't have a table. However the table has the advantage of "pushing" what is below it down so it doesn't overwrite it.