Hi Darwin,
Instead of looping through all the cells, use the RANGE.Find method.
Here's a modification of your routine (I commented out lines I replaced)
that I think will have the same result, but uses RANGE.Find for speed:
Sub reportdata()
'Paste the data according to the date of report
Dim dt As Integer
'Dim i As Integer
'Dim datarow As Integer
Dim rpdate As String
Dim SearchRange As Range
Dim Found As Range
'Get the date of report need to finish (without data)
Sheets("Summary").Activate
'datarow = Range("A" & Rows.Count).End(xlUp).Row
Set SearchRange = Range(Cells(166, 1), Cells(Rows.Count,
1).End(xlUp)).Offset(ColumnOffset:=2)
'For i = 166 To datarow
Set Found = SearchRange.Find("", SearchRange.Cells(SearchRange.Rows.Count,
1), xlValues, xlWhole)
'If IsEmpty(Cells(i, 3)) Then
'rpdate = Format(Left(Cells(i, 3).Offset(0, -2).Value, 10),
"yyyy-mm-dd")
'Exit For
'End If
'Next
If Not Found Is Nothing Then
rpdate = Format(Left(Found.Offset(0, -2).Value, 10), "yyyy-mm-dd")
Sheets("PivotTable").Cells(2, 10).Value = rpdate
dt = Sheets("PivotTable").Cells(2, 12).Value
'20', 40, RF marshalling btw CY/CFS and MR/CMR AND disc/load btw BH/V/L
and MR/CMR
Sheets("PivotTable").Range("L4").Resize(12, 74).Copy
Worksheets("Summary").Cells(dt, 2).PasteSpecial (xlPasteValues)
Else
'There were no blanks to find....
End If
End Sub
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Darwin Chan
Sent: Monday, December 19, 2011 9:52 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Instead of using Isempty(), what other ways can be
used for filling up the data
Dear all,
I have created a report to copy the data from a sheet to another. However, i
use isempty to check where to paste the data, but I found there is
performance issue.
Is there any other way out other than using Isempty()??
File: http://www.sendspace.com/file/64pseo
Below can find the code and also the file.
Sub reportdata()
'Paste the data according to the date of report
Dim dt As Integer
Dim i As Integer
Dim datarow As Integer
Dim rpdate As String
'Get the date of report need to finish (without data)
Sheets("Summary").Activate
datarow = Range("A" & Rows.Count).End(xlUp).Row
For i = 166 To datarow
If IsEmpty(Cells(i, 3)) Then
rpdate = Format(Left(Cells(i, 3).Offset(0, -2).Value, 10),
"yyyy-mm-dd")
Exit For
End If
Next
Sheets("PivotTable").Cells(2, 10).Value = rpdate
dt = Sheets("PivotTable").Cells(2, 12).Value
'20', 40, RF marshalling btw CY/CFS and MR/CMR AND disc/load btw BH/V/L and
MR/CMR
Sheets("PivotTable").Range("L4").Resize(12, 74).Copy
Worksheets("Summary").Cells(dt, 2).PasteSpecial (xlPasteValues)
End Sub
--
Darwin Chan
darwin.chankaw...@gmail.com
kw42c...@yahoo.com.hk
--
FORUM RULES (934+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
