Excel macro help

[jtr63]

I have a number of similar macros that insert a blank line when the value in a particular column changes, as below

Sub InsertRowAtChangeInValueColumnA()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub

Sub InsertRowAtChangeInValueColumnB()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub

I would like to use a variable for the column rather than just creating new variants. Can anyone help me with how to do this ?

TIA
John

[ReformedCharacter]

Does this help?

https://stackoverflow.com/questions/198 ... -to-number

RC

[chas49]

This works when the active cell is placed in the column you want to test:

Sub InsertRowAtChangeInValue()
Dim lCol As Long
Dim lRow As Long
lCol = ActiveCell.Column
For lRow = Cells(Cells.Rows.Count, lCol).End(xlUp).Row To 2 Step -1
If Cells(lRow, lCol) <> Cells(lRow - 1, lCol) Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub

[mc2fool]

I would like to use a variable for the column rather than just creating new variants. Can anyone help me with how to do this ?

Ok, but the question is where are you going to get/set that variable from? E.g. you could have:

Sub InsertRowAtChangeInValueColumn(sCol As String)
    Dim lRow As Long

    For lRow = Cells(Cells.Rows.Count, sCol).End(xlUp).Row To 2 Step -1
        If Cells(lRow, sCol) <> Cells(lRow - 1, sCol) Then Rows(lRow).EntireRow.Insert
    Next lRow
End Sub

And then call it with InsertRowAtChangeInValueColumn("A"), but that really just moves the issue ... although that could be ok in itself.

Or, of course, you could call InsertRowAtChangeInValueColumn(sTheColToUse) and then fill sTheColToUse from somewhere.

Note that you can also use column numbers, so if instead you declare the procedure as Sub InsertRowAtChangeInValueColumn(lCol As Long), and change all the sCols to lCols, then you can more easily loop through a set of them.

[modellingman]

I have a number of similar macros that insert a blank line when the value in a particular column changes, as below

Sub InsertRowAtChangeInValueColumnA()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub

Sub InsertRowAtChangeInValueColumnB()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub

I would like to use a variable for the column rather than just creating new variants. Can anyone help me with how to do this ?

TIA
John

You are using a couple of VBA "entities", Cells and Rows. These are actually properties of the Application object, so would be more fully written as Application.Rows and Application.Cells (see https://docs.microsoft.com/en-us/office ... ion(object)). As a special case, VBA allows you to use these properties without an object qualifier whereas generally you'd get an error when attempting to use a property without its object qualifier.

As properties of the Application object, both Cells and Rows each deliver a Range object (see https://docs.microsoft.com/en-us/office ... nge(object)).
Cells represents all the cells on the active worksheet and Rows represent all the rows.

Parts of a Range object can be accessed through the object's Item property. So Range.Item("A1") refers to cell A1, but because Item is the default property of a Range object, this can be abbreviated to Range("A1").

The Range.Item property is quite flexible in terms of its arguments - there can be one or two of these and you can mix numeric and A1 style notation (see https://docs.microsoft.com/en-us/office ... range.item).

Your Cells(lRow, "B") is actually shorthand hand for Application.Cells.Item(lRow, "B") and the second argument (the "B") uses A1 style notation. You could equally have used 2 or a variable such as lCol which was previously assigned a value of 2 in place of the "B" so the abbreviated form would be Cells(lRow, 2) or Cells(lRow, lCol). This is part of what mc2fool has suggested and my contribution is simply to add some of the underlying features of Excel's object model to explain why the suggestion works.

If you are planning on doing a lot of stuff with VBA to manipulate Excel workbooks, it is definitely worth developing some understanding of Excel's object model (the hierarchy of objects along with each object's properties and methods) that make up the Excel application. The Range and Application objects are fairly central to this, though the Range object can be a bit daunting as many of its properties (eg Item and EntireRow, both of which your code uses) deliver a different Range object.