Hi,
SpecialCells might work well. I haven't compared the performance.
SpecialCells(xlCellTypeBlanks) would be a more exact corollary to IsEmpty()
than my solution, since they both require the cells to be completely empty,
without even a formula in them that returns an empty string and without a
single quote (indicating an empty string of text) -- nothing.
Find only needs to search through the range until the first blank it finds,
whereas SpecialCells "searches" the entire range and returns a range object
with the union of all the cells of the given type. Perfect if you need to
search the entire range and do something with all of those cells anyway, or
when you are interested in a property of the cells that SpecialCells can
identify pretty quickly (blanks, errors, etc.) but no other method can find
as fast. However, it's possible that Excel handles cells without data in
them in such a way that it can very quickly find all truly empty cells and
the SpecialCells method, even though it returns more information than
required, would in that case be quickest.
If you want to try it, I think you could use the following revision to the
code I provided, off the top of my head. A few other suggestions here to
speed things up too. Composed in email; if you try this and there is some
problem/typo just post back for fix.
Sub reportdata()
'Paste the data according to the date of report
Dim dt As Long
Dim rpdate As String
Dim SearchRange As Range
Dim Found As Range
Dim wsSummary As Worksheet, wsPivotTable As Worksheet
Dim ScreenUpdateState As Boolean
'Get the date of report need to finish (without data)
Set wsSummary = Sheets("Summary")
With wsSummary
'.Activate
Set SearchRange = .Range(Cells(166, 1), .Cells(Rows.Count,
1).End(xlUp)).Offset(, 2)
'... the following line use RANGE.Find to find the first blank ...
'Set Found = SearchRange.Find("",
SearchRange.Cells(SearchRange.Rows.Count, 1), xlValues, xlWhole)
'... the following line uses RANGE.SpecialCells to find the first blank
...
Set Found = SearchRange.SpecialCells(xlCellTypeBlanks).Cells(1,1)
If Not Found Is Nothing Then
ScreenUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False
rpdate = Format(Left(Found.Offset(, -2).Value2, 10), "yyyy-mm-dd")
Set wsPivotTable = Sheets("PivotTable")
wsPivotTable.Cells(2, 10).Value2 = rpdate
dt = wsPivotTable.Cells(2, 12).Value2
'20', 40, RF marshalling btw CY/CFS and MR/CMR AND disc/load btw
BH/V/L and MR/CMR
' ... Copy/Paste Method ...
'wsPivotTable.Range("L4").Resize(12, 74).Copy
'wsSummary.Cells(dt, 2).PasteSpecial (xlPasteValues)
'... Value2 = Value2 Method ... may or may not be faster than
copy/paste ... does not overwrite the windows clipboard ...
wsSummary.Cells(dt, 2).Resize(12, 74).Value2 =
wsPivotTable.Range("L4").Resize(12, 74).Value2
Application.ScreenUpdating = ScreenUpdateState
End If
End With
Set SearchRange = Nothing
Set Found = Nothing
Set wsPivotTable = Nothing
Set wsSummary = Nothing
End Sub
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Sanjib Chatterjee
Sent: Tuesday, December 20, 2011 12:35 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Instead of using Isempty(), what other ways
can be used for filling up the data
please check the below mentioned code
Sub Macro_del()
'
'
' Keyboard Shortcut: Ctrl+q
'
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
ActiveWindow.SmallScroll Down:=-15
End Sub
On Tue, Dec 20, 2011 at 12:25 PM, Asa Rossoff <a...@lovetour.info> wrote:
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
--
-
--
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
