Macro help

T

TimmerSuds

I have the following macro that inserts a new column and pastes SLS int
the new column. The way the macro is currently set is to paste it int
rows 2-33.

I need it to paste SLS into all rows in Column G that are not blan
(this can vary from 1 to 500 rows).

Here's the current macro. I'd appreciate any advice
Thanks!

Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "SLS"
Range("C1").Select
Selection.Copy
Range("C2:C33").Select
ActiveSheet.Past
 
M

Mallycat

TimmerSuds said:
I need it to paste SLS into all rows in Column G that are not blan
(this can vary from 1 to 500 rows).
Click to expand...
Not Blank? or blank? How will you know how many rows there are? yo
need a column that you know will have data in the last row. I'l
assume that Column A has data in every row. Also, is the data in col
before you insert the new column or after? You will need to swap th
code around if it is before.


Sub UpdateSLS()
Dim myRange As Range
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Value = "SLS"
Range("C2:C33").Value = "SLS"
LastRow = Range("A600").End(xlUp).Row
myAddress = "G1:G" & LastRow
Set myRange = Range(myAddress)
For Each cell In myRange
If cell.Value <> "" Then cell.Value = "SLS"
Next cell

End Su
 
K

Ken Hudson

Hi,
Try this code:

Dim Iloop as Integer
Sub PasteSLS()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
For Iloop = 1 to 500
If Not IsEmpty(Cells(Iloop,"C")) then
Cells(Iloop,"C") = "SLS"
End IF
Next Iloop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Your code has column C but your message talks about column G? If it is
column G, you'll need to code the code accordingly.
 
D

Don Guillett

You code can be greatly simplified but you never told us how to determine
the last row.
Unless?, you really did mean the last row in col G instead of C. Then use
the 2nd.

Sub insertandfill()
Columns(3).Insert
Range("c1").Value = "SLS"
Range("c1").AutoFill Destination:=Range("c1:c33")
End Sub

Sub insertandfill()
lr=cells(rows.count,"g").end(xlup).row
Columns(3).Insert
Range("c1").Value = "SLS"
Range("c1").AutoFill Destination:=Range("c1:c" & lr)
End Sub
 
Top