Insert Rows based on Countif results

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

NBVC

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

ADVERTISEMENT

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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615

ADVERTISEMENT

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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Thanks G.I.A.

And also thanks for the article....it is interesting.
 

Forum statistics

Threads
1,137,367
Messages
5,681,081
Members
419,950
Latest member
BeckiJae

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
Top