Author Topic: VBA in Excel  (Read 4806 times)

0 Members and 1 Guest are viewing this topic.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #30 on: January 23, 2008, 02:15:07 PM »
18103
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA in Excel
« Reply #31 on: January 23, 2008, 02:17:21 PM »
WOW!   :-o

Dnereb

  • Guest
Re: VBA in Excel
« Reply #32 on: January 25, 2008, 09:11:18 AM »
The reason for doing the loop 6 times is simple in itself...

If you delete a row you are on.... you are on the next row (the original is gone)
if you loop on and move to the next row... You just skipped one!

solution run your loop from end to start instead.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #33 on: January 25, 2008, 09:14:39 AM »
actually, when you delete the row your on, the one below moves up and your on it.  The test still runs and tests the now current row and the new one below it.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

ML

  • Guest
Re: VBA in Excel
« Reply #34 on: January 25, 2008, 04:03:09 PM »
CM

I have not read the entire post; I'm just weighing in here
There a few methods you can use

xlshiftup (down)

And the row object (like cells) may have an offsett method as well.

Mark

Dnereb

  • Guest
Re: VBA in Excel
« Reply #35 on: January 26, 2008, 03:31:06 PM »
Actually it doesn't test again after deletion:

Snippet of solution code:
 
Code: [Select]
Rows(intRow).Select
                    ' And delete it!!
                    Selection.Delete
                End If
                ' Increase the row counter by 1
               'intRow = intRow + 1

Ml is correct as well using xlshiftup(down) will prevent this logic error as well.
but If I had to choose I would prefer looping end to start end inspecting the row below
Code: [Select]
If Cells(intRow, 8) >= Cells(intRow - 1, 8) ThenThis will reduce the instructions by 1 and in a loop run this often that is a legal argument to me.

(edit: added code tags to single line of code)

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: VBA in Excel
« Reply #36 on: January 28, 2008, 09:19:48 AM »
Actually it doesn't test again after deletion:
Actually it does, in fact it will continue to test until the 2 lines dont match.  That is the purpose of the while/wend.  I guess i should remove the extra comments from the code as it looks like it is confusing people about when the counter is being incremented.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

ML

  • Guest
Re: VBA in Excel
« Reply #37 on: January 28, 2008, 09:27:47 AM »

I wish there was a row and column (delete,insert) event.
I was trying to make something happen for weeks in my one spread sheet.
I was on The MSDN Forums and all; I got a lot of great suggestions but was still never satisfied with the result.
The one thing I did l learn and as someone in the forum also suggested; that is using a named range when you know that rows or columns are likely to be inserted; this allows a dynamic update to your range.

I think I am a bit off topic here; sorry, I am just thinking out loud.
I could probably do something with NamedRange.usedRange
If the NamedRange.UsedRange = +1 or something like that.

Named Ranges are priceless in Excel