Dear Shrini,
As Anil implied, the problem with your macro is with the "overhead" with
copying the data.
When you use:
Sheets("Blends Procesed").UsedRange.Copy Destination:=ws1.Range("a1")
not only are you copying the data, but you're copying all of the formatting too.
Not only that, you are copying it TWICE.
It copies it once to the clipboard, then it copies the clipboard to your
desitinatio worksheet.
I took your macro and added a "timer" function to it.
I then ran it on my workstation and it took 3 minutes and 41 seconds.
I then wrote the macro below in which, instead of clearing sheet3, I simply
delete it and create it new.
I then only copy the columns that you resulting macro had.
This macro ran so fast, the timer shows 0 minutes, 0 seconds!
Now, you may wish to add some formatting, but that would still be better than 3
minutes!
BTW: Excel will attempt to recognize any entry in a date format as a "date"
and convert it to an official "Excel date".
That is, the number of days since 1/1/1900.
That way, it can be displayed in a variety of formats.
It also will automatically convert the display to a "regional" format.
For instance: my dates are in m/d/yyyy format.
While your Regional date format may be dd-mm-yyyy, and excel will change the
display to suit.
I noticed that some dates (for records 140923-140931 and 141143) aren't
recognized as dates.
Here's my macro:
Sub BALANCE()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim Data
Dim oRow As Integer
tstart = Timer
'------------------------------
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Sheets("Blends Procesed").Select
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Sheet3").Delete
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet3"
Set ws = wb.Sheets("Blends Procesed")
Set ws1 = wb.Sheets("sheet3")
On Error GoTo 0
ws1.Cells(1, "B").Value = ws.Cells(1, "B").Value
oRow = 1
For Each Data In Sheets("Blends Procesed").UsedRange.Rows
If (Sheets("Blends Procesed").Cells(Data.Row, "Y").Value <> "") Then
oRow = oRow + 1
ws1.Cells(oRow, "A").Value = ws.Cells(Data.Row, "A").Value
ws1.Cells(oRow, "B").Value = ws.Cells(Data.Row, "B").Value
ws1.Cells(oRow, "C").Value = ws.Cells(Data.Row, "C").Value
ws1.Cells(oRow, "D").Value = ws.Cells(Data.Row, "Y").Value
End If
Next Data
'------------------------------
tstop = Timer
tElapsed = tstop - tstart
tMin = tElapsed \ 60
tSec = tElapsed Mod 60
MsgBox "Finished" & Chr(13) & tMin & " min " & tSec & " sec"
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Shrinivas Shevde <shrinivas...@gmail.com>
>To: अनिल नारायण गवली <gawlianil8...@gmail.com>
>Cc: excel-macros@googlegroups.com
>Sent: Thursday, December 11, 2014 6:28 AM
>Subject: Re: $$Excel-Macros$$
>
>
>
>Dear Anil
>Thanks for reply I was out of station for 2 week so I could not reply
>Please find attached workbook.
>When I run the micro it works well but very slow takes 3 to 4 mins for results
>Please go through it and let me know where is problem?
>As I Said earlier it was working fine before.
>Open file select STOCK sheet click on CTS Stock/Grade Stock button It will
>work but will take a tim
>
>Waiting for reply
>
>Regards
>Shrini
>
>
>On Thu, Nov 27, 2014 at 2:58 PM, Anil Gawli <gawlianil8...@gmail.com> wrote:
>
>Pl share the workbook with us
>>
>>
>>On Thu, Nov 27, 2014 at 2:56 PM, Shrinivas Shevde
>><shrinivas...@gmail.com> wrote:
>>> Dear All
>>> Please find below macro.
>>> Previously it was running very fine but from last few days it takes time (2
>>> to 3 min).I could not trace out can any one help me for finding out
>>> problem.
>>>
>>> Macro is just for copying some data from one sheet and paste to other shhet
>>> and removes unwanted column
>>>
>>> Sub BALANCE()
>>> Application.ScreenUpdating = False
>>> Dim wb As Workbook
>>> Dim ws As Worksheet
>>> Dim ws1 As Worksheet
>>> Dim rawdata
>>>
>>> Set wb = ThisWorkbook
>>> Sheets("Blends Procesed").Select
>>> Set ws1 = wb.Sheets("sheet4")
>>> ws1.UsedRange.Clear
>>> Sheets("Blends Procesed").ListObjects("Table3").Range.AutoFilter Field:=25,
>>> Criteria1:= _
>>> "<>"
>>> Sheets("Blends Procesed").UsedRange.Copy Destination:=ws1.Range("a1")
>>> ws1.Select
>>> Columns("D:x").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Cells.Select
>>> Cells.EntireColumn.AutoFit
>>>
>>> Sheets("Blends Procesed").Select
>>> Range("A2").Select
>>> Selection.AutoFilter
>>> Sheets("Sheet4").Select
>>> Range("K3").Select
>>> ws1.Select
>>> End Sub
>>>
>>>
>>> --
>>> Shrini
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
>>> not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to mailto:excel-macros%2bunsubscr...@googlegroups.com.
>>> To post to this group, send email to excel-macros@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/excel-macros.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>--
>
>Shrini
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
