Doubt selecting completed cells as RANGE

[Gopalakrishnan]

From my worksheet, I just wanted to copy completed cells from used range (** used columns) which would have more than 100 rows but minimum 5 or 10 cells only would contain values. Here I wanted to copy those completed values from activesheet and need to paste another book. Already I have designed code for pasting and other stuffs but got struck up with selecting completed cells. Below is code I designed to do that job and with this I'm getting subscript out of range error while running, please do the require!

Sub DeleteEmptyRow1(File)
Dim i, j, k As Integer
Dim LR, LC As Integer
Workbooks(File).Activate
LC = ActiveSheet.UsedRange.Columns.Count
LR = ActiveSheet.UsedRange.Rows.Count
For i = LC To 1 Step -1
If ActiveSheet.Cells(1, i).Value = "DESIGNNOTE" Then
'If cri2 = 1 Then
For j = LR To 1 Step -1
If ActiveSheet.Cells(j, i).Value = "" Then
Else
k = i - 1
Range(Cells(j, k), Cells(j, i)).Select ' I'm getting error @ this line only
ThisWorkbook.Sheets("Sheet2").Activate
MsgBox "Entering into loop"
ActiveSheet.Range("A1").PasteSpecial
End If
Next
End If
Next
End Sub

 

[technicalupload]

Hi Gopala,

This is working absolutely fine in my system. Excel 2010. Also I have added a line after "Range(Cells(j, k), Cells(j, i)).Select" which is Selection.Copy.

The results are pasted in sheet2 succesfully. I don't know why it is not working on your machine. Try this code on some other machine.

Regards...

 

[nuked]

try changing your integers to longs

 

[nuked]

also k could possibly resolve to 0 (= i - 1, where i could be 1), which would error on your line

 

[VoG]

Also it should be

Dim i As Long, j As Long, k As Long
Dim LR As Long, LC As Long

 

[Gopalakrishnan]

Thanks everyone,,,,, it worked! I just changed the variable declaration to LONG and it resulted in successful design