row insert problem

[project manager]

I have this code, its all ok unitl the last if part.

I'm trying to add a number of rows at the bottom populated row depend on the
number in J and last cell.

any help cheers?

Sub InsertDataintoBlank()

IngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow
If Trim(Range("A" & lngRow)) <> "" Then
strData = Range("A" & lngRow)
Else
Range("A" & lngRow) = strData
End If
Next

If Range("A" & IngLastRow).Value > 0 Then

Rows(IngLastRow, IngLastRow + (Range("J" & IngLastRow).Value)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Else
End If

End Sub

 

[JLGWhiz]

Two things:

1. This line has a typo:

For lngRow = 1 To lngLastRow

It has a lower case L instead of upper case I (i). Use courier font to
detect.

2. This line:

Rows(IngLastRow, IngLastRow + (Range("J" & IngLastRow).Value)).Select

Change to:

Rows(IngLastRow & ":" & IngLastRow + (Range("J" & IngLastRow).Value)).Select

This assumes that there is an integer value in the cell in column J.

 

[Jacob Skaria]

Hi, I have missed the declaration part in my original code...I use to prefix
the variables like the below....Use the below code to modify.. Also use
Option Explicit to avoid such issues. If you are looking to continue the
filling upto the value mentioned in Col J say (J1) you can straight away
replace the first line in the code to say lngLastRow = Range("J1")....Cheers
"Project Manager"..

lng for Long
int for Integer
str for String
bln for Boolean and so on..........

Option Explicit

Dim lngRow as Long
Dim lngLastRow as Long
Dim strData as String

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow
If Trim(Range("A" & lngRow)) <> "" Then
strData = Range("A" & lngRow)
Else
Range("A" & lngRow) = strData
End If
Next



If this post helps click Yes