PDA

View Full Version : Solved: Clean up some code



Djblois
01-18-2007, 10:52 AM
is it possible to put these lines of code into one line? Or if not 1 line, how about 2 lines?

Range("IV2").Select
Selection.End(xlToLeft).Select
Range(Selection, "A1").Select

ska67can
01-18-2007, 11:19 AM
Range(Range("IV2").End(xlToLeft), "A1").Select

ska

Djblois
01-18-2007, 12:28 PM
That works perfectly. Is it possible with this code also?

Range("D2").End(xlDown).Select
ActiveCell.Offset(0, 1).Resize(1, 3).Select
Range(Selection, Selection.End(xlUp)).Select

Norie
01-18-2007, 01:14 PM
Can we see the rest of the code?

There shouldn't be any need for any of this selecting or for using Selection.

If you really want to select:


Set rng = Range("D2").End(xlDown).Offset(, 1).Resize(1, 3)
Set rng = Range(rng, rng.End(xlUp))
rng.Select

Djblois
01-18-2007, 01:30 PM
oh then I use the code to put borders around it. Nothing spectacular.

Norie
01-18-2007, 01:34 PM
Well if that's all your doing there is no need to select.


Dim rng As Range
Set rng = Range("D2").End(xlDown).Offset(, 1).Resize(1, 3)
Set rng = Range(rng, rng.End(xlUp))
With rng.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

lucas
01-18-2007, 02:16 PM
Daniel needs to learn to take just a little more time and help us by stating what he is trying to do and then post the code he's using....instead of posting his problems as he codes them....please try Daniel. Folks will be a lot more willing to help you if you would not leave us guessing as to what your trying to do.....

It would also be nice if you read some of the other posts in the forum too. If you had you would have known that selection is discouraged as it is unnessary to perform the task....usually.

Djblois
01-18-2007, 02:21 PM
I know Selection is discouraged that is why I was trying to get away from it. I was just stating the code that I already had, not the code that I wanted. Also , I try to explain everything that I think is needed to understand my problem. Sometimes I don't explain well and I explain it better next time. I really can't post all my code. It is about 9 modules and 8 userforms = Close to a thousand lines. So, When I am a little stumped I ask for help. I am still a beginner and I appreciate all the help but I am trying to learn also.

Norie
01-18-2007, 02:27 PM
1000 line of code?:eek:

Whenever I hear that it sets the alarm bells ringing.

I'm not saying the code is wrong, but using 3 lines just select a range doesn't seem right.

I know the code I posted used 2 lines, not much of a difference but the only code you've posted so far has been for selecting.

If we could see a fuller example rather than just snippets perhaps we could give some pointers.

lucas
01-18-2007, 02:28 PM
is it possible to put these lines of code into one line? Or if not 1 line, how about 2 lines?

Range("IV2").Select
Selection.End(xlToLeft).Select
Range(Selection, "A1").Select
This is from your 1st post in this thread

We don't expect you to post all of your code in that situation but you could have let us in on what your doing with the selection....
All I am trying to say Daniel is to state what your trying to do.......you did not do that in post 1 of this thread.....this thread was viewed 36 times and very few people posted replies.....Just asking you to try to make your questions as clear as possible......take just a little more time at the onset....when you first post. It will help us lots and folks will be more willing to contribute solutions for you....that all I'm saying.

Djblois
01-18-2007, 02:40 PM
It is over a 1000 lines because I have set up an Add-in to use at my company to do reporting. It makes Pivottables in a little more than a minute (Professionally looking). What would normally take an experienced person about 15 minutes and an inexperienced person either can't do it or it will take well over 1 Hour. My add-In gives my users over 100 options to chose from in the pivottable. Since I have about 10 users and continually climbing, I keep adding features and also I try to speed it up. That is why I asked today, cause I knew selecting is a bad idea. I also try to protect if from any error you might think of, at least I try.

Norie
01-18-2007, 02:47 PM
Sorry, but that still doesn't explain why so many lines.

What I'm thinking is that if you are using the type of code you posted then
there's probably some redundancy there.

Again I'm not saying it's wrong, and if it works it works.:)

PS There is actually a limit to the amount of code you can have in a module.

Djblois
01-18-2007, 02:51 PM
Thanks for the tip. That is why I have about 8 different Modules. I think most of my code is optimized. I may be wrong. Those are the only two places I thought I can make it smaller. However, ever now and then I come up with a few ideas to make it smaller. I am not an expert but I try.

Djblois
01-18-2007, 02:55 PM
An example to what makes it so long is if you look at this post:

http://vbaexpress.com/forum/showthread.php?t=10925

The code I had before I started this causes two problems:

1) Minor Problem - Reports if take place within the same year are one line too long
2) Major Problem - I think it is causing a major error in 3 of my reports.

If I can get that code too work it will fix that problem.

Norie
01-18-2007, 05:59 PM
I'm sorry but I don't see how that explains why the code is so long.

Maybe I'm missing something, but all the code you've posted so far is only
a couple of lines.:)

Djblois
01-18-2007, 07:03 PM
Tommorow While I am at Work I will post a taste of my code.