Insert New Rows based on COUNTA()

  • Thread starter Thread starter TheDPQ
  • Start date Start date
T

TheDPQ

This might not be the right area however i am a newbie to Excel so
figure i'd start here. I have very limited experience with Excel s
excuse me if i don't make sense.
ANY HELP IN THE RIGHT DIRECTION WOULD BE AWESOM


I have a Column full of keywords, and i have a column full of Ad IDs..
is there a way in a *new* sheet to insert the keywords in as many time
as there are ads?

SHEET
Keyword 1 | 123
Keyword 2 | 456
xxxxxxxxx | 789

SHEET
Keyword 1 | 123
Keyword 1 | 456
Keyword 1 | 789
Keyword 2 | 123
Keyword 2 | 456
Keyword 2 | 789


I found COUNTA() Which lets me count how many keywords and ads i have
i'm just not sure what to do with those numbers now
 
You could use a macro:

Option Explicit
Sub testme()
Dim myCol1 As Range
Dim myCol2 As Range
Dim myCell1 As Range
Dim myCell2 As Range
Dim wks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long

Set wks = Worksheets("Sheet1")

With wks
Set myCol1 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set myCol2 = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))
If myCol1.Cells.Count * myCol2.Cells.Count > .Rows.Count Then
MsgBox "Too much data!"
Exit Sub
End If
End With

Set newWks = Worksheets.Add

oRow = 0
For Each myCell1 In myCol1.Cells
For Each myCell2 In myCol2.Cells
oRow = oRow + 1
With newWks.Cells(oRow, "A")
.Value = myCell1.Value
.Offset(0, 1).Value = myCell2.Value
End With
Next myCell2
Next myCell1

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top