Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Simplifying VBA Code (XL2000 VBA)

    I've probably asked this question before but I don't recall the answer - is there a simpler one liner that would replace the following?:

    ActiveCell.Offset(EditRow, 1).Value = ""
    ActiveCell.Offset(EditRow, 2).Value = ""
    ActiveCell.Offset(EditRow, 3).Value = ""
    ActiveCell.Offset(EditRow, 4).Value = ""
    ActiveCell.Offset(EditRow, 5).Value = ""
    ActiveCell.Offset(EditRow, 6).Value = ""
    ActiveCell.Offset(EditRow, 7).Value = ""
    ActiveCell.Offset(EditRow, 8).Value = ""
    ActiveCell.Offset(EditRow, 9).Value = ""
    ActiveCell.Offset(EditRow, 10).Value = ""
    ActiveCell.Offset(EditRow, 11).Value = ""
    ActiveCell.Offset(EditRow, 12).Value = ""
    ActiveCell.Offset(EditRow, 13).Value = ""
    ActiveCell.Offset(EditRow, 14).Value = ""
    ActiveCell.Offset(EditRow, 15).Value = ""
    ActiveCell.Offset(EditRow, 16).Value = ""
    ActiveCell.Offset(EditRow, 17).Value = ""
    ActiveCell.Offset(EditRow, 18).Value = ""
    ActiveCell.Offset(EditRow, 19).Value = ""
    ActiveCell.Offset(EditRow, 20).Value = ""
    ActiveCell.Offset(EditRow, 21).Value = ""
    ActiveCell.Offset(EditRow, 22).Value = ""
    ActiveCell.Offset(EditRow, 23).Value = ""
    ActiveCell.Offset(EditRow, 24).Value = ""
    ActiveCell.Offset(EditRow, 25).Value = ""
    ActiveCell.Offset(EditRow, 26).Value = ""
    ActiveCell.Offset(EditRow, 27).Value = ""
    ActiveCell.Offset(EditRow, 28).Value = ""
    ActiveCell.Offset(EditRow, 29).Value = ""
    ActiveCell.Offset(EditRow, 30).Value = ""
    ActiveCell.Offset(EditRow, 31).Value = ""
    - Ricky

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying VBA Code (XL2000 VBA)

    Well, you might try this:

    <pre> For i = 1 To 31
    ActiveCell.Offset(EditRow, i).Value = ""
    Next
    </pre>


    You will have to put this into a sub or procedure.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simplifying VBA Code (XL2000 VBA)

    Mike -

    Thanks, again. You've been a big help and the last tip has allowed me to trim hundreds of lines from the sub.
    - Ricky

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simplifying VBA Code (XL2000 VBA)

    FWIW you could also use something like:
    Range(ActiveCell.Offset(editrow, 1), ActiveCell.Offset(editrow, 31)) = ""
    which might run faster if you have a large range to do.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simplifying VBA Code (XL2000 VBA)

    Rory - Thank you for the response - anything to speed things up really helps. I had 8 large ranges that needed to be cleared before new data was written to a database from a userform.

    As it stands, when the user clicks [FINISH] from a multi-page userform, it takes more than 30 seconds for the routine to do it's thing! Ouch! I know someone more experienced than I could accomplish the same thing with half the code. I bet the sequence of events within a routine has a lot to do with speed as well. I've come across an excel utility: "code cleaner" - Do you know anything about it?

    To Andrew's credit, he's been warning me for months about the size of my project.

    Thanks, again.
    - Ricky

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simplifying VBA Code (XL2000 VBA)

    I believe there are several utilities around with that name - there's one from Microsoft which I think exports all code modules to text files, deletes them and then recreates 'clean' ones. I don't think this will speed up your code - it's more to avoid corruption. There are various tips for optimising code, which you can find in any good code book. I imagine they also figure on most of the usual Excel/VBA sites. As an example, in the original code you posted you repeatedly refer to ActiveCell - you should generally try and avoid repeated references to the same object by using the With statement.
    The problem with optimising code is not always in making your code run as fast as possible, but in determining what is the fastest way of achieving your goal! I often find it to be a matter of trial, error and experience. I was recently reviewing a workbook I created a couple of years ago and couldn't believe some of the things I'd done in it - there was nothing technically wrong with it, but there were much better ways (which I didn't know about then) of achieving what I wanted.
    Also, I know it's not always feasible, but I generally believe that if you're dealing with complex userforms and large databases, it's probably time to move to an actual database package! (sorry if that's a sore point! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying VBA Code (XL2000 VBA)

    How about:

    <pre> Range(ActiveCell.Offset(EditRow, 1), ActiveCell.Offset(EditRow, 31)).Value = ""
    </pre>

    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simplifying VBA Code (XL2000 VBA)

    Thanks - I'm in WAY too deep to change directions and I'm too old and too confused to begin learning to use a database. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Will these two examples do the same thing and is the first example the preferred one? Is this the correct use of the with statement?

    <font color=blue>Sheets("DB").Select
    Range("a1").Select
    With ActiveCell
    .Offset(EditRow, 1).Value = Val(Box_HH)
    .Offset(EditRow, 2).Value = Val(BoxTix)
    .Offset(EditRow, 3).Value = Val(BoxLate)
    .Offset(EditRow, 6).Value = Val(BoxGross)
    .Offset(EditRow, 7).Value = Val(BoxAdj)
    End With</font color=blue>


    Sheets("DB").Select
    Range("a1").Select
    ActiveCell.Offset(EditRow, 1).Value = Val(Box_HH)
    ActiveCell.Offset(EditRow, 2).Value = Val(BoxTix)
    ActiveCell.Offset(EditRow, 3).Value = Val(BoxLate)
    ActiveCell.Offset(EditRow, 6).Value = Val(BoxGross)
    ActiveCell.Offset(EditRow, 7).Value = Val(BoxAdj)
    - Ricky

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying VBA Code (XL2000 VBA)

    Yes, they do the same thing. However, unless there is some other reason to select the sheet and cell, the preferred code would be:

    <pre> With Sheets("DB").Range("A1")
    .Offset(EditRow, 1).Value = Val(Box_HH)
    .Offset(EditRow, 2).Value = Val(BoxTix)
    .Offset(EditRow, 3).Value = Val(BoxLate)
    .Offset(EditRow, 6).Value = Val(BoxGross)
    .Offset(EditRow, 7).Value = Val(BoxAdj)
    End With
    </pre>

    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying VBA Code (XL2000 VBA)

    You could also try the following:

    Range(cells(1,1),cells(10,1)).value = ""

    Mario

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •