Paleo
01-08-2005, 04:58 PM
Hi people,
I am having an annoying VBA problem. I have a spreadsheet that has many Subs on its 52 Mb and uses data from another worksheet that has 48 Mb and a text file that has another 11 Mb.
I have a VBA code that works fine on it but is TOO slow:
Sub Test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
Dim i As Long, n As Long
n = 2
Range("A1").EntireRow.Copy Sheets(Nova).Range("A1")
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & i).Value = "NC" Then
Range("A" & i).EntireRow.Copy Sheets(Nova).Range("A" & n)
n = n + 1
Range("C" & i).EntireRow.Delete
End If
Next i
Sheets(Nova).Activate
Range("A1:F21").Select
End Sub
And when I try to modify it to make it faster, using the code below I get an error 1004 message, telling me an error occurred at the copy function from the range class.
Sub test2()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
Dim filterRng As Range
Set filterRng = Nothing
With Sheets(aNome)
Set filterRng = .Range("A1", .Range("K65536").End(xlUp))
With filterRng
.AutoFilter field:=1, Criteria1:="=NC"
.SpecialCells(xlCellTypeVisible).Copy Sheets(Nova).Range("A1")
.SpecialCells(xlCellTypeVisible).Delete
.Cells(1).EntireRow.Insert
End With
Sheets(Nova).Range("1:1").Copy .Range("1:1")
End With
Set filterRng = Nothing
Sheets(Nova).Activate
ActiveWorkbook.Names.Add Name:="tblNC", RefersToR1C1:="=NC_nov!R1C1:R21C6"
Range("A1").Select
Sheets(aNome).Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
The interesting part is that I get that error AFTER it has copied all the cells.
:dunno :dunno :dunno :dunno
So, does anyone knows whats going on? May anyone help me out, please?
:help :help :help :help
I am having an annoying VBA problem. I have a spreadsheet that has many Subs on its 52 Mb and uses data from another worksheet that has 48 Mb and a text file that has another 11 Mb.
I have a VBA code that works fine on it but is TOO slow:
Sub Test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
Dim i As Long, n As Long
n = 2
Range("A1").EntireRow.Copy Sheets(Nova).Range("A1")
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & i).Value = "NC" Then
Range("A" & i).EntireRow.Copy Sheets(Nova).Range("A" & n)
n = n + 1
Range("C" & i).EntireRow.Delete
End If
Next i
Sheets(Nova).Activate
Range("A1:F21").Select
End Sub
And when I try to modify it to make it faster, using the code below I get an error 1004 message, telling me an error occurred at the copy function from the range class.
Sub test2()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
aNome = ActiveSheet.Name
Nova = "NC_" & Right(aNome, 3)
Dim filterRng As Range
Set filterRng = Nothing
With Sheets(aNome)
Set filterRng = .Range("A1", .Range("K65536").End(xlUp))
With filterRng
.AutoFilter field:=1, Criteria1:="=NC"
.SpecialCells(xlCellTypeVisible).Copy Sheets(Nova).Range("A1")
.SpecialCells(xlCellTypeVisible).Delete
.Cells(1).EntireRow.Insert
End With
Sheets(Nova).Range("1:1").Copy .Range("1:1")
End With
Set filterRng = Nothing
Sheets(Nova).Activate
ActiveWorkbook.Names.Add Name:="tblNC", RefersToR1C1:="=NC_nov!R1C1:R21C6"
Range("A1").Select
Sheets(aNome).Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
The interesting part is that I get that error AFTER it has copied all the cells.
:dunno :dunno :dunno :dunno
So, does anyone knows whats going on? May anyone help me out, please?
:help :help :help :help