Separating Dates With Blank Rows

[Justinian]

I have a massive spreadsheet with after-hours air requests from tenants but the dates are not spearated. i want to be able to quickly separted 5/8 from 5/9, 5/9 from 5/10, etc., so that I can have a blank row under each date for a total.

How can i quickly do this? VBA??

 

[MrKowz]

Sure can! Give this a shot:

Public Sub SplitDates()
Dim i   As Long, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("H" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    If Month(Range("H" & i - 1).Value) <> Month(Range("H" & i).Value) Then
        Rows(i).Insert shift:=xlDown
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Justinian]

****, you're good! How did you learn to write like this?

 

[MrKowz]

****, you're good! How did you learn to write like this?

Thanks!

I've taken a basic VBA class in college, but I attribute 99% of what I know in VBA to the forums, here. I've been an active member for a few years now, and I just absorb all the information I'm given.

Ask questions, learn from the answers, toy with other's code to see what each element does, and use the macro recorder. I can't even begin to tell you how helpful the macro recorder is, even to a veteran coder.

 

[Justinian]

That's an answwer I hear often but you know how to write a lot of code in there, that must have taken a long time. I peruse these forums and have for almost two years and I still don't uderstand the syntax and vernacular of VBA. I must be doing something wrong.

Are you learning from reading other posts or from posting your own questions?

 

[MrKowz]

That's an answwer I hear often but you know how to write a lot of code in there, that must have taken a long time. I peruse these forums and have for almost two years and I still don't uderstand the syntax and vernacular of VBA. I must be doing something wrong.

Are you learning from reading other posts or from posting your own questions?

I ask a lot of questions, and I read other posts. In the beginning, I was always trying to come up with what I thought was the "optimal" solution... then an MVP would post...

I would then look at the MVP's response, pick it apart, and ask questions about that code in the thread. If I see something particularly interesting/helpful, I would write it down on a sticky note and put it up on my monitor (I've since upgraded to using a 37" TV as my monitor when I'm at home... there are a lot of sticky notes...)

If you spend enough time on the forums, you'll see a lot of the same questions come around, just in a slightly different form; adapting to those different forms is the rough part to get used to.

That being said, if you ever have any questions regarding code I've provided for you, feel free to ask. I'm more than happy explaining things more in depth so that it is better absorbed.

 

[Justinian]

Cool! I gues the two things I don't understand are:

Dim i As Long, _
LR As Long

And then this:

For i = LR To 3 Step -1
If Month(Range("H" & i - 1).Value) <> Month(Range("H" & i).Value) Then
Rows(i).Insert shift:=xlDown
End If
Next i

I never really got clear definitions on when to use and what DIM is or the LR, or i stuff.

 

[MrKowz]

Dim is for declaring your variables. It is always good programming habit to declare every single variable you use (check out the help file for Data Types for an good explanation of the various types you can declare variables as). I have a somewhat unorthodox way of declaring my variables that I acquired from Greg Truby, which I'll explain a bit more in depth at a later time (possibly in a PM).

LR is a variable I commonly use to refer to the "Last Row" of the data.

i is a "looping" variable. I like to use i, j, k, l, m, n as looping variables simply due to other programming I've learned. It seems to be a somewhat universal norm to use those letters as looping variables.

In the code I provided, we found the Last Row by looking in Column H. The line of code: LR = Range("H" & Rows.Count).End(xlUp).Row is a great univeral line of code to find the last row. "H" refers to the column it is looking in for the last row. Rows.Count returns the very last row in the spreadsheet (65536 in Excel 2003, 1048576 for Excel 2007, and something larger for Excel 2010). Using .End(xlUp), that looks upward, from that VERY last cell in the column to find the first populated cell (ie, it skips every blank). Then .Row returns the row of the range object returned.

Next, the loop. Looping through a range is something that is VERY common to see in VBA code. It is identifiable by either a For/Next loop or a Do While/Until Loop. Here, we are looping one-by-one using a For-Next loop. Since we are altering the number of rows by inserting (or deleting), we need to loop backwards through the code (Step -1) to prevent items from being skipped over (which, again, I'll explain at another time).

So basically, the line "For i = LR to 3 Step -1" is telling the code to start at LR (the last row), and go to 3, by incrementing i by step -1.

I'd recommend playing with the code on a copy of your sheet, and changing various things in the code. See what you can come up with.

 

[Justinian]

Wow, that is a lot of info! I am still learning what variables are, so you surmise my VBA expertise is minimal at best.

I tried playing arounf with code once and I kept getting debug errors because frankly, I don't know coding language well enough to know what words / phrases I can insert into the module.

 

[texasalynn]

and my two cents

I agree with MrKowz, that by reading through the posts you can pick up alot of information. I started out by asking alot of questions. Then I have several books and take a few on-line courses. Then I started teaching classes. So all of these have helped in learning VBA and excel functions. So just keep on asking questions and reading posts. It really does start to make sense.

 

[Justinian]

Thanks, guys. I am glad there are so many people here to help me since all my issues are for work.