• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert multiple rows at change in value

K

KathrynJ

New Member
I have a file where column A has values in varying number of rows - for example:

City Name (Header on row 6)
- Chicago (has 14 rows with this value)
- New York (has 6 rows with this value)
- Atlanta (has 25 rows with this value)
etc.

I want to insert 2 blank rows at each change in value.

And then, on the 1st blank row that was added after each value, how can I Count the number of rows that have that value and show the results in that cell?

Currently, I'm using the following macro to insert 1 row after each change in value, but don't know how to adjust the macro - or find a better one to use.

Dim iRow As Integer, iCol As Integer
Dim oRng As Range

Set oRng = Range("a7")

iRow = oRng.Row
iCol = oRng.Column

Do

If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
Cells(iRow + 1, iCol).EntireRow.Insert Shift:=xlDown
iRow = iRow + 2

Else
iRow = iRow + 1
End If

Loop While Not Cells(iRow, iCol).Text = ""
 
Attached is a test file. The first worksheet (Data) shows the original data; the second worksheet (Results) shows how I want the results to look.
 

Attachments

  • Test File for Metrics.xlsm
    14.1 KB · Views: 5
Have you considered using the SubTotal function built into Excel. Look at the attached where I have used that function.
 

Attachments

  • Test File for Metrics.xlsm
    15 KB · Views: 9
Alan, thanks for the suggestion. I did think about using the Subtotal function, but it complicates with the next steps I need to take with this data.
 
Narayan,
I copied the macro and added it to my actual workbook. When I try running it, I get an error message that says "invalid use of the me command". Here's the code where it's used - in 3 places.

Application.ScreenUpdating = False

firstrow = 6
If Me.AutoFilterMode Then Cells.AutoFilter
lastrow = Me.Range("A" & Rows.Count).End(xlUp).Row

Set inputdatarange = Me.Range("A" & firstrow & ":D" & lastrow)

And then again later in the code - 2 more times:

Me.Cells.AutoFilter
For i = numberofrows To 2 Step -1
Me.Cells(filteredrows(i) + i - 1, 1).EntireRow.Insert
Next

Suggestions on how I can fix this?
 
KathrynJ said:
Narayan,
I copied the macro and added it to my actual workbook. When I try running it, I get an error message that says "invalid use of the me command". Here's the code where it's used - in 3 places.

Application.ScreenUpdating = False

firstrow = 6
If Me.AutoFilterMode Then Cells.AutoFilter
lastrow = Me.Range("A" & Rows.Count).End(xlUp).Row

Set inputdatarange = Me.Range("A" & firstrow & ":D" & lastrow)

And then again later in the code - 2 more times:

Me.Cells.AutoFilter
For i = numberofrows To 2 Step -1
Me.Cells(filteredrows(i) + i - 1, 1).EntireRow.Insert
Next

Suggestions on how I can fix this?
Click to expand...
Hi ,

The keyword Me is supposed to be used to refer to the section where the code has been placed ; if the code has been placed in a Worksheet section , the keyword can be used to refer to the worksheet , and hence can be used to qualify the Range keyword.

When you place the code in a Module , instead of using the Me keyword , use Activesheet.

Ensure that when you run the code , the appropriate worksheet is activated.

Narayan
 
Sub Count_Change()

Dim I As Integer
Dim StrtRow As Integer

Dim rng As Range
StrtRow = 6
Do While ActiveCell.Value <> ""

If ActiveCell.Offset(1, 0).Value <> ActiveCell.Value Then
Set rng = Rows(ActiveCell.Row)
For I = 1 To 2
rng.Offset(1).Insert xlDown
Next
ActiveCell.Offset(1, 0).Value = WorksheetFunction.CountIf(Range("A1" & ":A" & ActiveCell.Row), ActiveCell.Value)
ActiveCell.Offset(I + 1, 0).Select

Else
ActiveCell.Offset(1, 0).Select
End If


Loop


End Sub


This is another one if you want to use (Option is always better ;))

You can change for Loop pertaining to I based on how many rows gap you desire.
 
NARAYANK991 said:
Hi ,

The keyword Me is supposed to be used to refer to the section where the code has been placed ; if the code has been placed in a Worksheet section , the keyword can be used to refer to the worksheet , and hence can be used to qualify the Range keyword.

When you place the code in a Module , instead of using the Me keyword , use Activesheet.

Ensure that when you run the code , the appropriate worksheet is activated.

Narayan
Click to expand...
Thank you! It now works beautifully.
KathrynJ
 
You must log in or register to reply here.
Back
Top