Insert Rows based on Countif results

NBVC

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Hi everyone,

I am trying to insert rows based on the result of Countif's. How would I go about doing this using VBA....still trying to grasp many VBA concepts.

In Sheet1 I have a bunch of names in A1:A30, many are duplicates.

In Sheet2 I have a unique list of names that are found in sheet 1. This list is in A1:A5

Starting at A10, I want to insert the name in A1 and insert rows below = the number of times that name is found in Sheet1!A1:A30. Immediately following I will insert next name and repeat procedure until all names have been cycled through.

Thanks in advance.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
In most cases I think a better approach would be to rather than insert lines 4,5,6,7, just plop the next unique guy in cell A8. I'll stick with your original idea though below.

You can put the countif results in a cell - preferably a named range cell. In VBA you can access cell contents a la

dim i as Long, NumInsertRows as Long
NumInsertRows = Range("MyCountIfResult")
for i = 1 to NumInsertRows

You can optimize this by doing a single insertion vs. the (sometimes catastrophically) slower one-by-one suggested above.

Consider qualifying Range("MyRange") as Range("MyRange").value or .Value2 or .Formula or ... well you get the idea. There's a rich set of info available in the properties - not for what you're doing now, but keep it in mind.

However you CAN use worksheet functions in VBA code. I do it so rarely that I don't won't try to speak definitively on it, but you can go e.g.
foo = Application.CountA(SomeSheet.Range("A:A"))

So hopefully you've got some options to play with now.
 
Upvote 0
Hello G.I.A,

Thanks for your response.

I know there are probably many ways around getting the solution and I do appreciate your suggestions, but I don't really want to place results temporarily in cells, though. I want to do the whole thing through VBA. In fact, ultimately I want to place another value (result of a formula) beside each name in each row inserted, and the names can and will change around a lot, so entering a name at a time manually isn't really going to work.

Here is a little bit of a pseudocode of what I am after.

Code: 
Range("A8").select
for i = 1 to 5
    Count=Countif(Sheet1!A1:A30,A1)
     for j = 1 to count
          insert row
          insert a formula in column B of active row (e.g. IF(B(activerow)="","",1)
     next j
next i
 
Upvote 0
Quick thoughts as I have other worldly crises descending:

Consider using .Offset property in code, from some starting point ("A1" or some named range). It is analogous to worksheet's OFFSET.

You CAN use a single named range and keep reusing it as you loop in code. (If so, consider clearing it when done to avoid confusion.)

"Application." is vast and powerful, but (I've heard) massively slow or resource-intensive. Give it some playing around with. (But you won't really be using VBA in a purist sense - just as a worksheet function wrapper :( )

.Offset is the way to go when writing data into cells from code.
Range("myrange").Offset(i, 0) = sMyString
will populate one cell; (i,1) goes one column to the right.

Tight on time here ... others, jump in.
 
Upvote 0
I am trying to write the code myself...but not getting very far.

Here's what I've got. It is not doing as I expected. Can anyone tell me why not?

By the way, I am not avoiding using Offset()....but I still trying to understand the basic Visual formula manipulation stuff first.

Code: 
Sub AddRowsOfData()

Dim i As Integer, j As Integer, count As Integer
Dim StartRow As Integer, Name As String

'initiate starting row for data entry
StartRow = 8
Range("A" & StartRow).Select

For i = 1 To Application.WorksheetFunction.CountA(Range("Sheet2!A1:A5"))
    count = WorksheetFunction.CountIf(Range("Sheet1!a1:a30"), "A" & i)
    Name = Range("A" & i).Value
    For j = StartRow To (StartRow + count)
         ' ActiveSheet.Range("A" & j).Select
         ActiveSheet.Range("A" & j).Value = Name
         ActiveSheet.Range("B" & j) = WorksheetFunction.IsText("A" & j)
         ActiveSheet.Rows(j + 1).Insert
    Next j
    StartRow = i + 1
Next i

End Sub

Data Sheet:
Book1
ABCD
1john
2jack
3mark
4john
5fred
6
7
8
9john
10
11
12
13
14john
15mark
16
17jack
18jack
19mark
20
21
22jack
23
24
25
26john
27
Sheet1


Summary Sheet (to start):
Book1
ABCD
1john
2jack
3mark
4fred
5
6
7
8
Sheet2


...continued on next posting
 
Upvote 0
What I expect:
Book1
ABCD
1john
2jack
3mark
4fred
5
6
7
8john
9john
10john
11john
12john
13jack
14jack
15jack
16jack
17mark
18mark
19mark
20fred
21
Sheet2


What I am actually getting after running the macro above:
Book1
ABCD
1john
2jackTRUE
3TRUE
4TRUE
5
6mark
7fred
8
9
10
11johnTRUE
12
13
14
15
16
Sheet2
 
Upvote 0
Hi,

Code: 
Sub test()
Dim ws1     As Worksheet
Dim ws2     As Worksheet
Dim i       As Byte, StartCell  As Range
Dim nList   As Range, Ce        As Range

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set nList = ws2.Range("A1:A4")
Set StartCell = ws2.Range("A8")

For Each Ce In nList
    i = Application.WorksheetFunction.CountIf(ws1.Columns(1), Ce)
    If i > 0 Then StartCell.Resize(i, 1) = Ce
    Set StartCell = StartCell.Offset(i, 0)
Next Ce
End Sub

HTH
 
Upvote 0
Thanks very much Kris!

Pretty much what I was looking for.:)

Now, how would I update the macro to auto insert a formula in column B adjacent to each entry made in column A....I am trying to understand your line of code:

Code: 
If i > 0 Then StartCell.Resize(i, 1) = Ce

for argument's sake...how would i insert formula: =If(Left(A(i)="J","Y","N")

where A(i) is the occupied cells in column A
 
Upvote 0
The use of Resize here is unconventional. He's using it to seed a range. I always go back to this article.
http://www.avdf.com/apr98/art_ot003.html

The relative advantage of some (but not all) its techniques may have eroded over time; if someone wants to test his techniques I'd like to know how much they pay off now, for given Excel versions and CPU speeds. I really admire this article.

For the column B you can just copy the cell using .copy and .paste, or set formulas with .Formula, such as
Code: 
    Dim i As Long, j As Long, c As Range 'c actually means cells, which truly are ranges
    i = 0
    j = 12 'the number of them to seed
    For Each c In range("B1:B" & j)
        i = i + 1
        c.formula = "=If(Left(A" & i & ",1)=""J"",""Y"",""N"")"
    Next
 
Upvote 0
You must log in or register to reply here.

Similar threads

RichTom44
  • Question Question
VBA/Excel Formula to insert rows
Replies
3
Views
356
Peter_SSs
Peter_SSs
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
262
sijpie
sijpie
L
  • Question Question
Insert and copy dynamic rows between workbook A and workbook B
Replies
1
Views
218
Anthony47
A
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
300
stuartgb100
S
C
  • Solved
VBA macro: insert row if condition is met and apply formulae
Replies
3
Views
313
CXsjr
C

Forum statistics

Threads
1,223,832
Messages
6,174,905
Members
452,590
Latest member
CraiginColorado

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top