VBA code I wrote this week in Excel and how it helped me

  • Article

OK—my title is kind of colloquial. But I'm in a hurry, and the title conveys the idea well enough.

Here's the deal: I have a spreadsheet with a bunch of important data. But, some of the most distinctive data are all merged into one cell. To make the data more usable, I needed to parse the data elements in that cell and separate each out into its own column. That way, I can filter and sort for each specific element. VBA made this possible and in short order (see my recent post Why VBA Still Makes Sense).

Here's what the column looked like before:

And, here's what it looks like after:

You can see that the data that used to be in one column and now they are in three columns.

Here's the code I wrote to make it happen:

1 Dim strParty As String

2 Dim strPV As String

3 Dim strDist As String

'Step through rows of data one at a time

7 Dim FinalRow As Integer

8 Dim counter As Integer

9 Dim curCell As Range

11 FinalRow = Range("A2", Range("A2").End(xlDown)).Rows.Count

13 For counter = 1 To FinalRow

14 curCell = Worksheets("Sheet1").Cells(counter, 7)

15 strParty = Mid(curCell.Text, 5, 1)

16 strPV = Mid(curCell.Text, 32, 1)

17 strDist = Mid(curCell.Text, 41, 4)

19 curCell.EntireRow.Cells(1, 8).Value = strParty

20 curCell.EntireRow.Cells(1, 9).Value = strPV

21 curCell.EntireRow.Cells(1, 10).Value = strDist

22 Next counter

16 lines of actual code to loop through thousands of rows in a single moment. It saved me tons of time and it made my data way more usable.

Let's take a quick look at what the code means.

Lines 1-3 are just setting up my placeholders to temporarily contain the values of the three separate data items in the column for a given row.

Lines 7-9 are placeholders for values for the looping routine. Final Row and curCell depend on how many rows are in the spreadsheet and also which row I'm on.

Line 11 establishes how many total rows need to be looped through.

Lines 13-21 is where the work occurs. For each row, the code grabs the cell in the unparsed column of the worksheet. Then, it parses out the first, second, and third values. The code places these values in the variable placeholders. Finally, it takes the values in those variables and puts them each in their own column cell for that same row.

The code then loops through the next row.

Now, I knew how to code this because I've been doing this kind of thing for years. But, for people who are new, just pressing F1 brings up a rich help system for VBA coding. It contains examples, explanations, and other resources to help you get there. Your first VBA code may stretch you, but your skills will grow rapidly.

Rock Thought of the Day: Annie Lennox

I'll never forget watching Annie Lennox sing "Here Comes the Rain Again" during a music awards show back in the 90's. Previously, a lot of so-called "artists" had gone out and lip-sync'd/guide-vocal'd their way through their songs. Then, Annie comes out and shows them how it's really done. She laid down a version of this song that remains fixed in my mind as one of the best performances I've ever seen. My only regret was that I was not there to see it live. Every singer should find videos of her live performances and study them. I wish I could find that performance on YouTube. No luck so far.

Rock On