Getting macro to recognise exactly how many rows there are in a column...and other issues...

[markaval]

Hi,

I've spent a whole day trying to figure out a way of solving this issue and I'm stuck, so I'm hoping one of you gurus will be able to help.

ISSUE ONE
I've compiled a complicated macro to format text data that gives statistics for Tour De France stages:

1. It looks at a text file imported into column A. Each stage has varying number of riders, hence varying rows.
2. The format of each line is 1. Position, 2. Surname(s), 3. First name(s), Team Name, Time (mins and secs) in a single string.
3. The macro is designed to pull out each of the above elements, and in terms of separating out the elements, works perfectly, taking into account multiple surnames, multiple first names, different time formats and other anomalies in the original data.

The issue comes with the number of rows in each column.

The macro by default selects the whole of the column, so for example, if I had 300 rows of data in column A, the macro will work fine for exactly 300 rows of data in each of the other columns, but more or less rows for subsequent imports of data and the macro sticks rigidly to the 300 row number.

I want the macro to adjust all columns dynamically to the number of the rows in column A (whether or not there are blanks in the other columns, which there are in some). I tried inserting some code to look at the end of the data in column A. This worked fine but the macro did not recognise the code for the other columns and I couldn't work out how to make it work (my knowledge of VBA is zilch - next project ). For example in column C the macro correctly extracted the entries for times for the 300 rows but then carried on inserting 0s for a zillion more rows in the column, which is what I didn't want!

Question: Is there a way to get the other columns to sense check the number of rows in column A and then apply that value so that all the other columns have the same number of rows as column A for the duration of the macro?

ISSUE 2
My macro included some fairly serious nested 'if' statements to check whether a name was in upper or proper case and to decide whether a name had 1,2 or even three parts to a surname/first name. They worked perfectly in the initial build of the spreadsheet (pre-macro build) (with the exception of the problem I came across in ISSUE 3), but when I built the macro, when it compiled and failed and I checked the VBA code, some of the if statements had became corrupt/missed bits out - is this an issue anyone else has come across? I did manage to debug this, and I even tried rebuilding the macro again to see if it was just a glitch but the exact same issue occurred, and seemed directly related to the length of the nested ifs.

ISSUE 3
Nested ifs can be tricky little sobs (mine certainly were ). A couple of times I had to introduce additional conditions - the code worked perfectly when checking with F9 in specific cells, but the actual output of the cell was not always correct despite F9 telling me the code was correct! I was 100% sure the statement was correct, so as a last resort I cut and pasted the additional condition that I'd put at the end of the nested if and put it at the start of the nested if, and hey presto, the new condition functioned correctly. Is there a known issue with adding new conditions at the end of nested statements? Should I always add a new condition at the start of the nested if? I can't see any logical reason why but perhaps more experienced users may know of a reason why this happens.

Thanks in advance for help from the forum. Hope I've explained things reasonably clearly but if anyone needs more information then just let me know.

Mark

 

[MARK858]

Question: Is there a way to get the other columns to sense check the number of rows in column A and then apply that value so that all the other columns have the same number of rows as column A for the duration of the macro?

For example for Column E...

Range("E1:E" & Range("[B][COLOR="#FF0000"]A[/COLOR][/B]" & Rows.count).End(xlUp).Row)

or

Sub LASTR()
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("E1:E" & LastRow).Select
End Sub

 

[markaval]

Hi Mark858,

Thanks for the quick reply! I'll try this out and let you know how I get on - thanks again for your help.

Mark

 

[markaval]

Hi Mark858,

Well, you're a complete star! I used your first line of code and once I worked out where to put it, it worked a treat and ran a thousand lines of text correctly formatted in about three seconds

I've made up my mind to learn VBA - seeing how useful a simple line of code is in making things run smoothly has been an eye opener.

Thanks again for all your help.

Regards

Mark