PDA

View Full Version : Solved: Error on VBA Code in 600000 Row



parscon
04-02-2012, 02:43 PM
i have a file with 600000 Row and i use this code


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


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
04-02-2012, 02:51 PM
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
04-03-2012, 12:25 AM
I do not have a break in te data .

Paul_Hossler
04-03-2012, 12:02 PM
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



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


Paul

CatDaddy
04-03-2012, 12:22 PM
try:

lastrow = .Range("B65536").End(xlUp).Row

parscon
04-03-2012, 12:22 PM
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
04-03-2012, 12:30 PM
try:

lastrow = .Range("B65536").End(xlUp).Row

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

Thank you again .

jolivanes
04-03-2012, 12:49 PM
This went past 32768 without erroring.


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

jolivanes
04-03-2012, 05:33 PM
This might do the same thing but faster.


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

mohanvijay
04-03-2012, 08:24 PM
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
04-04-2012, 04:43 AM
Thanks to all people , Now it is ok .

jolivanes
04-04-2012, 02:31 PM
For the sake of us when encountering the same problem, what was your solution?

parscon
04-04-2012, 02:34 PM
I Use :Dim x As Long

and my problem sloved .