Topic: Excel and VBA Question

[ML]

I know this is for "ACAD" but I realize a lot of people on here do program with Excel as well; me being one of them
So I was wondering if anyone could answer this question for me?

I have macros, that read from very specific rows however one thing I never looked in to (and now need to) was; if the user inserts a new row, how can my macros dynamically adjust to the insert?

Here is a small example of something I did

Code: [Select]

Dim i As Integer

On Error Resume Next
'Check for unpaid bills
With Sheets(2).Select
 For i = 6 To 22
  If Range("N" & i).Value > 0 Then
   If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
    Then GoTo Continue Else
     Range("L6").Select
     Exit Sub
   End If
 Next i
End With

Now if the user inserts a row, my range would now need to be 6 to 23.

Is there a way to do this in VBA?

Thank you

Mark

[ML]

Or if it was written like this:

Code: [Select]

With Sheets(2).Select
  If Range(N6:N22).Value > 0 Then
   If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
    Then GoTo Continue Else
     Range("L6").Select
     Exit Sub
   End If
 Next i
End With




[Guest]

Now if the user inserts a row, my range would now need to be 6 to 23.

Is there a way to do this in VBA?

Thank you

Mark

I would probably use a named range: select some cells and click on INSERT -- NAME -- DEFINE and enter a name.  And just to test it, add a hyperlink to the newly created range.  Then click on the link - you'll see the range of cells highlighted.  Now add a new row and then click on the link again.  You'll see that the highlighted cells now include the inserted row.

[ML]

Matt

I don't hink that will adjust the macro in VBA.
Plus I don't want to take the chance of the named range changing either.

I need a way to increment the code using rows

For example.

If User Inserts a row
 Then row that has a macro using it +1
End If

There is an AllowRowInsert Method but I don't want to do that to the user
They "should" not be limited like that; so I need to think more dynamically

Thank you

Mark

[Guest]

Matt

I don't hink that will adjust the macro in VBA.
Plus I don't want to take the chance of the named range changing either.

I need a way to increment the code using rows

For example.

If User Inserts a row
 Then row that has a macro using it +1
End If

There is an AllowRowInsert Method but I don't want to do that to the user
They "should" not be limited like that; so I need to think more dynamically

Thank you

Mark

It won't adjust the code - and you don't need it to if you use a named range.  And the only way it could get renamed is if someone actually went through the motions of renaming it - if they even knew how to.

Could you post a sample XLS file?

[ML]

Sorry Matt

I respectfully disagree

I do think the code needs to be adjusted and I know it can be.

Possibly if I use a
Do While Loop

Hummmm
I need to look more into it

Thanks

Mark

[hendie]

fwiw I have to agree with Matt, named ranges are the way to go here. That's about the only way you can account for users changing/adding/deleting rows.
Changing a named range isn't something you average user would know how to do

[ML]

Hey Hendie

How are you?

Wait a minute; I think I missed something that Matt said

If I name a range and insert a row, the named range will pick up the inserted row; or there is a way to make it do so?

Thank you,

Mark

[Guest]

If I name a range and insert a row, the named range will pick up the inserted row...

Yup... The named range will auto-magically detect the inserted row.  Give it a shot!

[ML]

Ok

I will try it now Matt

Thanks

Mark

[Guest]

Ok

I will try it now Matt

Thanks

Mark

Sure.... NOW you'll try it.   

[ML]

LOL

Now I am saying WTF!!!!!!

My spread sheet is not allowing me to define a name
It has in the past

On Worksheet activate

I set allowinsertrows to = false

I think this has something to do with it; so I need to work that out first Uggggggggggg

Do you have macros that point a cell range?

How are they affected when you insert a row

Thank you,

Mark

[Guest]

Do you have macros that point a cell range?

How are they affected when you insert a row

Thank you,

Mark

You don't need a macro.  See the attached ZIP file.

[Guest]

If you wanted to select the range via code, all you need is this: Range("list_of_items").Select  where 'list_of_items' is the name of the range.

[ML]

Hummm

That is interesting

Under the help screen - Range Object,
I found this bit of code

While it is not precisely what I want; it is very close to what I am thinking

Mark

Code: [Select]

i = 1
    For Each a In Selection.Areas
        MsgBox "Area " & i & " of the selection contains " & _
            a.Rows.Count & " rows."
        i = i + 1
    Next a

The above code is using the count method but I am thinking of using the row insert method

Mark

[ML]

In my first example I might be able to say,

Pseudo code:
If row is inserted i = i +1

Mark

Code: [Select]

For i = 6 To 22
  If Range("N" & i).Value > 0 Then
   If MsgBox("Do you want to overwrite last week's bills?", vbQuestion + vbYesNo, "Checking Accounts") = vbYes & vbCrLf _
    Then GoTo Continue Else
     Range("L6").Select
     Exit Sub
   End If
 Next i


[hendie]

the thing is, if you go down that road, you need to know how many rows have been added/deleted.
With the named range, it doesn't matter.. it'll take care of itself

[ML]

Interesting!

Let me take a look at Matt's example and post back.

Hendie;
Then what happens to your macros? They go unchanged?
My spreadsheet has a lot of automation

Couldn't I say

For Each Row
 Then i = i +1
End If

[Greg B]

Can't you put something after the last row and have your loop

from 6 to EOL

or something like that?

[ML]

THat is what I started out looking to do Greg
Just not sure of the proper method

Mark

[ML]

Matt
I did look at your example; that is really cool and good to know; however, the link still either needs to be manually clicked or done through code.

I did record the macro as I ran through it --->

Code: [Select]

Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown
    Rows("9:9").Select
    Selection.Insert Shift:=xlDown
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown
    Rows("13:13").Select
    Selection.Insert Shift:=xlDown
    Rows("15:15").Select
    Selection.Insert Shift:=xlDown
    Rows("16:17").Select
    Selection.Insert Shift:=xlDown

Now, the one definite advantage of this method is that I can undo which I can not do (or know how to do) after running an Excel macro.
It is like grabbing a selection set in ACAD

Thank you again

I will play with it a bit more

Mark

[Guest]

This will find the last row that has any text in it.  Might be of some help to you.

Code: [Select]

   Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[a1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
So then it would be 6 to LastRow

[ML]

Obviously we could address this all in code
Including updating the range after an insert

Hummmmm

I need to take a closer look

Mark

[Greg B]

Nice Matt.

Your's defines the last row and right now you have a wild card character.

[ML]

It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING

Again, pseudo code:

On Worksheet change

 For each row in worksheet
   If a row is inserted or Rowinserted = True
     Each range in macro +1
   End If
 Next Row

So if the range is N6, it will now become N7

That would be dynamic

Mark

[Greg B]

It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING

Again, pseudo code:

On Worksheet change

 For each row in worksheet
   If a row is inserted or Rowinserted = True
     Each range in macro +1
   End If
 Next Row

So if the range is N6, it will now become N7

That would be dynamic

Mark

This seems to make it more difficult then it needs to be.

[Guest]

It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING

Again, pseudo code:

On Worksheet change

 For each row in worksheet
   If a row is inserted or Rowinserted = True
     Each range in macro +1
   End If
 Next Row

So if the range is N6, it will now become N7

That would be dynamic

Mark

Why are you making this more difficult than it needs to be?  Just use the range and call it a day? (unless I'm missing something??)

[Greg B]

It would be great if I can catch the insert row event (hummmmmmmmm)
I don't think there is such an event however, there is a worksheet change event - DING DING DING

Again, pseudo code:

On Worksheet change

 For each row in worksheet
   If a row is inserted or Rowinserted = True
     Each range in macro +1
   End If
 Next Row

So if the range is N6, it will now become N7

That would be dynamic

Mark

Why are you making this more difficult than it needs to be?  Just use the range and call it a day? (unless I'm missing something??)

He had trouble when trying to do this.

A couple posts back.

[ML]

OK, we'll do

Thank you everyone

Mark

[ML]

Hey guys

I did play with the named range quite a bit.

I saw how inserting a row with a named range will work with formulas and that is great to know,
still I am using a lot of VBA in this spread sheet.

I have this bit of code in the worksheet calculate event

Code: [Select]

Private Sub Worksheet_Calculate()

'Extra Cash Cells in Row 5 = Extra Cash Cells in Row 127
Range("C5").Value = Range("C127")
Range("D5").Value = Range("D127")
Range("E5").Value = Range("E127")
Range("F5").Value = Range("F127")
Range("G5").Value = Range("G127")

Well, even with a named range, inserting rows etc., it still will not pick up the change.
So, if you inserted a row a 127, then the cell C5 will become blank.

I am open to suggestions but I still believe that this will involve some dynamic coding of some sort

Thank you

Mark

[Guest]

Hey guys

I did play with the named range quite a bit.

I saw how inserting a row with a named range will work with formulas and that is great to know,
still I am using a lot of VBA in this spread sheet.

I have this bit of code in the worksheet calculate event

Code: [Select]

Private Sub Worksheet_Calculate()

'Extra Cash Cells in Row 5 = Extra Cash Cells in Row 127
Range("C5").Value = Range("C127")
Range("D5").Value = Range("D127")
Range("E5").Value = Range("E127")
Range("F5").Value = Range("F127")
Range("G5").Value = Range("G127")

Well, even with a named range, inserting rows etc., it still will not pick up the change.
So, if you inserted a row a 127, then the cell C5 will become blank.

I am open to suggestions but I still believe that this will involve some dynamic coding of some sort

Thank you

Mark

Change the hard-coded ranges to Named ranges and you should be all set.

[ML]

Hi Matt

Actually
I did do that but that even became un necessary

I just simply told C5 to Sum C127, creating a link between the two cell and so and that resolved it.

There is no need to even use VBA or The Calculate Event in this case as 127 has a formula in it.

I may use that approach in another spot on the worksheet

Still, what intrigues me is this:
When we insert a row, the formulas dynamically adjusts, so why can't VBA code do this?

Mark

[ML]

You are definitely right
It is easier to do something with a named range then to address individual cell (array) ranges.

I guess if we compared it to ACAD, it is like a selection set in ACAD; once we have a selection, we can filter and do some things.

I haven't had much time but I was also looking at the re size method.

Possibly something like:

Code: [Select]

With named range
 If row.resize (row.count +1) = True
  Then do something
 End If
End With

Understand, I have may be 3 different scenarios where this method needs to be employed; not just the one example that I posted which was easily taken care of.

Also, I don't have a problem posting the spread sheet except there is some personal financial info in there that I would have to first strip out.

Sound like I am gettting closer?

Thank you,

Mark

[Guest]

You are definitely right
It is easier to do something with a named range then to address individual cell (array) ranges.

Told you so!   

Keep at it!!

[ML]

Hey Matt

Definitely; named ranges are nice and much more dynamic.
I am now trying to figure out how to get to each row or cell in a named range.

Also,

If you name a range Sh1bills
With this bit of code:

Code: [Select]

Sub Sh1bills()

Set rng1 = Range("Sh1bills")
Set rng1 = rng1.Resize(rng1.Rows.Count + 1)

MsgBox rng1.Rows.Count
You can have the msgbox return the number of rows in the range.
If you insert or delete a row in the range and run the macro again, it will return the new row count.

This is a good start but I need a way to say, if row is inserted or deleted, then
 Do something

Any ideas?

Also, would you put that in the change event?

Thank you,
Mark

[Guest]

This is a good start but I need a way to say, if row is inserted or deleted, then
 Do something

Any ideas?

This might be of some help: http://www.dailydoseofexcel.com/archives/2006/08/21/capture-deleted-rows/

[ML]

Cool!

It looks good
I will have to look closer as soon as I have time

Thanks Matt!

Mark

[Guest]

Cool!

It looks good
I will have to look closer as soon as I have time

Thanks Matt!

Mark

No prob, Bob!...erm... Mark!  Sorry... been watching WAAAAY too much 'Bob the Builder' lately... because of.... the... kids.


Yeah... that's it!  The kids!   

[ML]

Hey! Do the kids know VBA?

[Guest]

Hey! Do the kids know VBA?

No, but they're well-versed in 'Cars', 'Sesame Street', 'The Muppet Show' (can't believe I got 'em hooked on THAT), 'Wow! Wow! Wubbzy!' and 'Oobi'.

[ML]

Hey, that's cool
How about I go watch Kermit the Frog with them and you finish this for me?