Deleting Rows in Excel with VBA

Deleting and shifting Rows up in VBA is a straightforward task that nevertheless hides a few traps. Say, any Row that has a numeric value in Column 'A' needs to be deleted and the Rows below shifted up. A VBA solution such as the one that follows does not work.

Option Explicit 

Sub DeleteRows()
    Dim LastRow, Ctr As Long  

    '// find the last Row with data
    LastRow = Cells(65536, 1).End(xlUp).Row

    '// loop through the Rows, deleteing Rows that have a
    '// numberic value in Column 'A' and shifting Rows up   

    For Ctr = 1 To LastRow
        If IsNumeric(Cells(Ctr, 1)) Then
            Cells(Ctr, 1).EntireRow.Delete Shift:=xlShiftUp
        End If
    Next Ctr
End Sub 

The problem is that if two or more consecutive Rows have numeric values in Column 'A', not all the Rows that need to be deleted get deleted. Why? Say that Rows 3 and 4 have numeric values in Column 'A'. When Row 3 is deleted, Row 4 moves up to take its place, in effect becoming Row 3. Meanwhile, the loop counter gets incremented in the next pass from 3 to 4, with the code scanning the value in Cell A4 but inadvertently skipping the value in Cell A3. 

An elegant solution to this problem is to start looping at the last Row with data in the Worksheet and move up one Row at a time, deleting and shifting Rows up when the required condition is satisfied. Here is the solution: 

Option Explicit 

Sub DeleteRows()
    Dim LastRow, Ctr As Long   

    '// find the last row with data
    LastRow = Cells(65536, 1).End(xlUp).Row   

    '// loop through the Rows, deleteing Rows that have a
    '// numberic value in Column 'A' and shifting Rows up    

    For Ctr = LastRow To 1 Step -1
        If IsNumeric(Cells(Ctr, 1)) Then
            Cells(Ctr, 1).EntireRow.Delete Shift:=xlShiftUp
        End If
    Next Ctr 
End Sub

The Joy of Running

My tongue-in-cheek remark to a friend after reading Christopher McDougall's Born To Run was this: "Marathons are for wimps". Now, I have yet to run a marathon and I have no illusions about the dedication, hard work, toughness and tenacity required to complete a marathon but after reading about 'ultra freaks' running up and down the 10,000+ feet high Colorado mountains for 100 miles, or plodding 135 miles across the floor of the Death Valley in California in 135 degrees heat, one can be forgiven for thinking that marathons are a stroll in the park.

Born To Run opens with an all too familiar story. A beefy former war correspondent who is built like someone that nature intended 'to take a bullet for the President' rather than pound down the pavement, Mcdougall is doing the rounds of sports shrinks to treat his dodgy feet. In the winter of 2003, he is in Mexico chasing a missing pop star for The New York Times Magazine when he stumbles across a picture of a Jesus like figure running down a rockslide. Welcome to the secret world of Tarahumara Indians, the greatest ultra runners the world has never heard about.

Living like invisible ghosts in the shadowy recesses of the Copper Canyons of Mexico, the peaceful Tarahumara tribes have for centuries outrun their Indian and European tormentors alike just to survive. What is the secret behind their superhuman prowess that enables them to run for hundreds of miles without rest? This is the question that persuades Mcdougall to abandon his celebrity hunt and start another type of hunt, the hunt for a mythical gringo named Caballo Blanco, the 'white horse' who lives with the Tarahumara and runs like one.

Before the book finishes, there is a gripping duel over 100 miles of punishing Colorado trails between a Community College teacher named Ann Tracy and top Tarahumara runners who have been coaxed to participate in the Leadville 100 ultramarathon with promises of corn bags for their villages. I felt the description of this race alone gave all the bang for my bucks.

The book climaxes with a 50 mile foot race in the treacherous Tarahumara territory between the cream of the Tarahumara tribe and the superstars of the North American - which is to say, the world's - ultramarathon scene.

And the secret to the Tarahumara success? In a sentence or three, it is this: All humans are hardwired to run. As kids, all of us run with joyful abandon. As we grow up, we lose this sense of playfulness and sheer joy in running. The Tarahumara keep this playful spirit alive into adulthood, and they have over the millenia woven the art of running into rituals that govern their daily lives. Also, they do not buy expensive, gel-filled Nike running shoes that seem to do more harm than good.

Shantaram: Crime and Redemption

If one has to summarise in three words Gregory David Roberts' 2004 debut novel Shantaram, then it has to be this: "Dostoevsky meets Bollywood". 

Or, that is what I thought after yet another scene in the novel in which Bombay's (when the story unfolds, the bustling Indian island city is about another couple of decades away from being re-named Mumbai in response to a Hindu nationalist popular groundswell) chic set banter and indulge themselves in one of the city's fashionable tourist haunts by the sea. 

And what a chic set it is. Roberts, who tells the story in first person, drinks, smokes, eats and philosophises with local Indian journalists, film producers, mafia enforcers, Iranian army deserters, washed-up Palestinian fighters, Pakistani spies, European outcasts, and an Afghan mafia boss who claims to have found the rational basis of morality.   

And just like Bollywood movies that the author claims to love, the novel packs a lot of action, and enough surprises to satisfy even the most demanding fan of Bollywood suspense thrillers. However, the book is much more than a pulp version of Bollywood. 

At the heart of the narrative is that eternal quest for meaning, love and redemption that inform all great works of art. Roberts, who escaped from a maximum security jail in Victoria while serving a sentence for armed robbery, finds all of these and more in the most unlikely of places and persons. 

Towards the very end of the 933 pages long book, Roberts declares: "The truth is that, no matter what kind of game you find yourself in, no matter how good or bad the luck, you can change your life completely with a single thought or a single act of love". 

One can only say "Amen!" to that and recommend the book as a must read.