Hi
The macro is based on a simple regular ( not jagged) 2 Dimensional range. In other words it is based on all rows having the same number of columns.
Any other situation is likely to cause problems. That problem that you have encountered may be the frist of many similar problems. Just solving that one problem won’t get us very far, as then tomorrow, or the day after, you will have another problem and so on, and so on…. A lot of time may be wasted writing macros that we then don’t need.
There are two things you need to do consider…
_1) Think very carefully of all the situations that may occur: Think of all the situations that might occur other than those resulting in a simple regular ( not jagged) 2 Dimensional range
_1b) Consider if the macro should only start when a regular ( not jagged) 2 Dimensional range , for example of minimum 2 columns of data occurs.
Will there likely be different numbers of columns?
Could there be empty columns within a row? – If so, how should that situation be handled??
Etc… etc…
_2) Think about whether an event macro ( one that starts automatically ) is the best for this situation. ( Event macros are actually quite advanced. When learning VBA one typically first meets event macros at a middle to advanced level )
Another possibility to consider is if it might be better to just have a normal macro. I mean by this a macro that does not start automatically. You would decide when to run the macro, for example via a button
I will have time to look again tomorrow. In the meantime, the more you can think of exactly what you want, and what situations might occur, the better chance we will have of getting it right without too many re writing of coding.
( A last thing to bear in mind. As the code given to you in a help forum becomes more complicated, it will be even more important for you to get to grips with learning and understanding the coding. In the future you wont have the support you might expect from having had more professional help..
Software is not as exact a science, as it probably should be, especially if Microsoft had anything to do with it!
The more complicated a coding is, the more chances are that problems occur in the future , even if you think nothing has changed. This is especially true if you choose to keep your Microsoft software up to date. Microsoft seem to be getting more and more incompetent and changes / updates seem to cause an increasing number of problems.
In short, it is getting more and more difficult to be just a user of software. To be a user, you need also to have some idea about the workings of any software you use… It probably should not be like that, but unfortunately it is… and increasingly so.
I am a builder of houses mostly these days. A manual labourer. . Previously I studied Physics at an advanced level, but I had no interest in computing. I needed Excel VBA for an important personal project a few years ago. I found, to my surprise, that it was not possible to use it effectively without getting quite knowledgeable about it.
I don’t understand why it is like that. One possibility is that most professional programmers are idiots, or they prefer to get things wrong, so that they will always be needed to repair the mess they make… )
Alan
Reply With Quote
Bookmarks