Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A blankline routine (Excel VBA XP)

    Hello,

    In a list of vendor's, I needed to insert a blank line in between each vendor's name to separate them. I created this little routine to do just that. I looks at the active cell, compares it to the cell above. If the values are the same, it continues down the list. If not, it inserts a blank line. So, if anyone needs to do this, here is one way to do it. As a beginner, it may not be the best way, so if Hans or John or anyone has a better way, please let me know:

    One assumption: I have the ActiveCell in cell A2 to begin with.

    Sub BlankLine()

    While ActiveCell > ""
    ActiveCell.Offset(1, 0).Select
    If ActiveCell = ActiveCell.Offset(-1, 0) Then
    ActiveCell.Offset(1, 0).Select
    Else
    Selection.EntireRow.Insert
    ActiveCell.Offset(2, 0).Select
    End If
    Wend
    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A blankline routine (Excel VBA XP)

    I would do it like this:

    <pre>Public Sub BlankLine()
    Dim I As Long
    With ActiveSheet.Range("A1")
    For I = ActiveSheet.Range("A65536").End(xlUp).Row - 1 To 1 Step -1
    If .Offset(I - 1, 0).Value <> .Offset(I, 0) Then
    .Offset(I, 0).EntireRow.Insert
    End If
    Next I
    End With
    End Sub
    </pre>


    You do not need to select any cells to accomplish this, and it is always more efficient if you don't select.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A blankline routine (Excel VBA XP)

    Legare,

    I'll certainly insert this code into my module. Thank you. Could you answer a few questions about it?

    Dim I As Long
    With ActiveSheet.Range("A1")
    For I = ActiveSheet.Range("A65536").End(xlUp).Row - 1 To 1 Step -1 ......what does .end(xlUp).Row - 1 to 1 Step-1 do?
    If .Offset(I - 1, 0).Value <> .Offset(I, 0) Then .....when you say value<>, where does it get the value from?
    .Offset(I, 0).EntireRow.Insert
    End If
    Next I
    End With

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A blankline routine (Excel VBA XP)

    I will try to answer your questions:

    The ActiveSheet.Range("A65536").End(xlUp).Row - 1 returns the offset of the last row in column A that is not empty. Therefore, I am setting up the loop to start at the last row and move up (the Step -1) to the top. If you start at the bottom and work up, you don't have to deal with rows below changing when you insert a new row.

    .Value is a property of a range object. It says to use the contents of the cell specified by the range. .Value is the default property of the range object, so it can usually be left out, but I think you have cleared code if you specify it.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •