First of all...
I think some of your primary assumptions are not accurate!
I have 109,000 rows with 74 columns in one of my reporting documents
(my macros build a summary "dashboard" based on this data)
I wrote a macro that:
a) prompts for a reporting month,
b) Clears the Report sheet
c) Extracts all records originated in the specified month
d) copies all 74 columns to the Report sheet.
The macro ran and copied 9700 rows in 3 seconds.
Now, your problem is a bit different because the date field isn't a "true"
Excel
date.
It's a string of characters that REPRESENTS a date to YOU.
Based on your example, your date(s) must be in "d.m.yyyy" format.
So, we need to split up the string and extract the "month" character.
By doing that, and comparing STRINGs instead of integers, I was able to
process 109,000 records in 4 seconds.
Yes, I know that was a 25% increase in CPU time, but if I remove the
StatusBar updates (which is display intensive)
I can recover the "wasted" second! (just kidding)
The following macro assumes:
- The source data is in a sheet called "Data2"
- The Destination sheet is called "Sheet1"
- The column that contains the "date" is column "N"
- The maximum column to be copied is column "BZ" (should be sufficient!)
You can modify it to fit your actual conditions.
Sub CopyRows()
Dim dRow, sRow, sRows
Dim sCol
Dim dMon As Integer
Dim dArray
Application.ScreenUpdating = False
dMon = InputBox("Enter Month", "Report Month", Month(Now) - 1)
tStart = Timer
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A2:BZ200000").ClearContents
dRow =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("A1:A100000"))
sRows =
Application.WorksheetFunction.CountA(Sheets("Data2").Range("A1:A200000"))
For sRow = 2 To sRows
If (sRow Mod 1000 = 0) Then Application.StatusBar = sRow & " of " &
sRows & " = " & Round(sRow / sRows * 100, 1) & "%"
dArray = Split(Sheets("Data2").Cells(sRow, "N").Value, ".")
If (dArray(1) & "X" = dMon & "X") Then 'uses "X" to convert integer
values to strings from dArray
dRow = dRow + 1
Sheets("Sheet1").Range("A" & dRow & ":BZ" & dRow).Value =
Sheets("Data2").Range("A" & sRow & ":BZ" & sRow).Value
End If
Next sRow
Application.ScreenUpdating = True
Application.StatusBar = False
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
msg = "Reported " & dRow - 1 & " Records in:"
tStop = Timer
TMin = 0
TElapsed = tStop - tStart
TMin = TElapsed \ 60
TSec = TElapsed Mod 60
msg = msg & Chr(13) & Chr(13)
If (TMin > 0) Then msg = msg & TMin & " mins "
msg = msg & TSec & " sec"
MsgBox msg
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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: Seba <sebastjan.hri...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, October 14, 2011 6:28:25 AM
Subject: $$Excel-Macros$$ Selecting a range based on a cell value
Hi all,
I am facing a problem regarding the range selection.
I have a worksheet used for recording daily stuff. SOmething like
this:
ColA ColB ColC
January 1.1.2011 9:00 - 10:00
January 1.1.2011 10:00 - 11:00
January 1.1.2011 etc.
January 2.1.2011
January 2.1.2011
January 2.1.2011
January 3.1.2011
January 3.1.2011
January 3.1.2011
etc.
Other columns contain daily specific data.
At the end of each month I have to create reports on what was done in
that month. Hence I need to copy the range for all stuff in that month
(for example January) and paste it to another worksheet in the same
workbook.
My problem is that I can't use fixed ranges, because of the February
29th.
I tried several things before posting here:
1) I wrote macro to copy and paste the entire range (all months) to
the new sheet and have it delete all entries which didn't contain the
month in question. This turned to be a pain due to 3500 and some rows.
2) I wanted to search for the required month and loop over all rows,
but this would also be time consuming.
3) Then I tried filtering the range to display only entries with
respective month, however I encountered date format issues:
Macro for April recordes like this:
ActiveSheet.Range("$A$1:$AS$3653").AutoFilter Field:=3, Operator:=
_
xlFilterValues, Criteria2:=Array(1, "4/1/2011")
I modified this to:
ActiveSheet.Range("$A$3:$IN$3653").AutoFilter Field:=3,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MyMonth)
Whereas MyMonth is the variable defined earlier so I can use one macro
for all 12 months.
Here the format is the problem. My locale entry convention is:
dd.mm.yyyy
But the macro syntax only works if I put in: mm/dd/yyyy, which is not
the format used in our country.
I also tried to modify the format with: MyMonth= Format(MyMonth, "mm-
dd-yyyy")
but I always get the date displayed with full stops and not with
slashes.
How can I fix this formatting issue or maybe in a more efficient way
select the required range for desired month?
Thank you for your help.
seba
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
