Hi there,
I am new to vba and I am trying to write a macro for transforming my data. I have been able to execute it successfully but the problem is that I want this macro to be repeated on cells that are 18 rows below. I tried to Google it but couldn't understand the concept of looping and how to enter looping in my code.
I tried this:
I need to repeat it for almost 2000 times so it would be great if someone could help me out.
Thanks in advance.
I am new to vba and I am trying to write a macro for transforming my data. I have been able to execute it successfully but the problem is that I want this macro to be repeated on cells that are 18 rows below. I tried to Google it but couldn't understand the concept of looping and how to enter looping in my code.
I tried this:
VBA Code:
Sub RangeVar()
Dim rng As Long
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasArray Then
rng = rng + 18
End If
Next cell
Range("B2").Value = rng
End Sub
but it doesn't do what I need.
Here's my macro:
Sub insertRow2()
'
' insertRow2 Macro
'
Range("A3:A19").Select
Selection.EntireRow.Insert
Call productSizes
Range("P2:AF2").Select
Selection.Copy
Range("Y3").Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:= _
False, Transpose:=True
Range("B2").Cut Destination:=Range("T3")
Range("A2").Cut Destination:=Range("B2")
Range("A2").Formula = "=LOWER(SUBSTITUTE(B2,"" "",""-""))"
Range("A2").Copy
Range("A3:A18").PasteSpecial xlPasteValues
Range("B2").Copy Destination:=Range("B3:B18")
Range("A3").Copy Destination:=Range("A2")
Call deleteimagerow
End Sub
Sub deleteimagerow()
Range("P2:AF2").Cut Destination:=Range("AZ2")
Range("y3:y19").Copy Destination:=Range("Y2")
End Sub
Sub productSizes()
Range("C2:K2").Copy
Range("I3").Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks:= _
False, Transpose:=True
Range("C2:K2").Select
Selection.ClearContents
Range("I3:I11").Cut Destination:=Range("I2")
Range("H2").Value = "Size"
End Sub
I need to repeat it for almost 2000 times so it would be great if someone could help me out.
Thanks in advance.
Last edited by a moderator: