Solved: Error on VBA Code in 600000 Row

[parscon]

i have a file with 600000 Row and i use this code

[vba]
With ActiveSheet

lastrow = Range("B1").End(xlDown).Row

x = 1

For i = 1 To lastrow
If .Cells(i, 1).Value = "" Then
.Rows(i).Copy
Sheets("Sheet2").Rows(x).PasteSpecial (xlPasteAll)
x = x + 1
End If
Next i
End With

[/vba]
This code will copy only the row that their B column have a data to Sheet 2

but it is work till 32000 row after that runtime error and in debug show

x = x + 1

how can fix this problem ?

Thank you .

[Aussiebear]

Does the data end here, or do you have a break in the data at this point?

Your last row is determined by looking down from row 1 whereas it should be looking up from the bottom of the sheet.

[parscon]

I do not have a break in te data .

[Paul_Hossler]

1. How are I and lastrow Dim-ed?

It looks like they should be Long

2. You were inconsistant with how you were refering to the ActiveSheet


[vba]
With ActiveSheet

lastrow = .Range("B1").End(xlDown).Row Need the dot

x = 1

For i = 1 To lastrow
If .Cells(i, 1).Value = "" Then This refers to Column A. Did you want .Cells(i,2) for Col B?
.Rows(i).Copy
Sheets("Sheet2").Rows(x).PasteSpecial (xlPasteAll)
x = x + 1
End If
Next i
End With
[/vba]

Paul

[CatDaddy]

try:

[vba]lastrow = .Range("B65536").End(xlUp).Row[/vba]

[parscon]

I user Dim lastrow As Long and on record 32768 I got this error :

and when click on debug show me

x = x + 1 and in hint show me x = 32768

Run-time error '6' :

Overflow

[parscon]

try:

[vba]lastrow = .Range("B65536").End(xlUp).Row[/vba]

When use this code it will not copy to sheet2 . that mean does not work

Thank you again .

[jolivanes]

This went past 32768 without erroring.

[vba]
Sub TryThisCodeB()
With ActiveSheet
Dim lastrow As Double
Dim x As Double, i As Double
Application.ScreenUpdating = False
lastrow = .Cells(Rows.Count, 2).End(xlUp).Row

x = 1

For i = 1 To lastrow
If .Cells(i, 1).Value = "" Then
.Rows(i).Copy
Sheets("Sheet2").Rows(x).PasteSpecial (xlPasteAll)
x = x + 1
End If
Next i
End With
Application.CutCopyMode = False
Sheets("Sheet2").Select
Application.ScreenUpdating = True
End Sub
[/vba]

[jolivanes]

This might do the same thing but faster.

[vba]
Sub TryThisCodeC()
Dim LR As Double
LR = Cells(Rows.Count, 2).End(xlUp).Row
With Range("A1:A" & LR)
.SpecialCells(xlCellTypeBlanks).EntireRow.Copy Sheets("Sheet2").Range("A1")
End With
End Sub
[/vba]

[mohanvijay]

I user Dim lastrow As Long and on record 32768 I got this error :

and when click on debug show me

x = x + 1 and in hint show me x = 32768

Run-time error '6' :

Overflow

check data type of variable "x"

that must be integer change it into long data type

maximum value for integer data type is 32767

[parscon]

Thanks to all people , Now it is ok .

[jolivanes]

For the sake of us when encountering the same problem, what was your solution?

[parscon]

I Use im x As Long

and my problem sloved .