MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Variable Range


Posted by Martin Brennan on September 05, 2000 5:11 AM

Most of my experience is using the macro recorder and then trying to improve it and speed up the process. I am new to a lot of this.

I have a spreadsheet which comprises three columns by, up to, 20,000 rows. The first column is date, second and third columns are prices. All data is entered in date order, several entries per day and I do not want to amend that order at all. Entries for any one date could range from 8 for the day up to 4000 for that day.

I want to be able to specify a range by entering a date in an input box and then using a VBA macro, find all entries for that date and transfer all info (i.e., A1:C10 or A1:C3705, say) to another spreadsheet for analysis.

I could use a loop, but I think it could take an age to complete, I think I can use "Find, xlDown", (to find first), followed by A65536, "Find, xlUp" (or something similar), but I wonder if there is a better way. Any bright ideas anyone?

TIA

Martin


Posted by Scott H on September 05, 0100 5:24 AM

Martin:

Try the AutoFilter command. Select the first cell in the date column, click on Data>Filter>AutoFilter. A drop down arrow will then appear in the cell allowing you to select any date that appears in the column. The nice feature is that the other columns will follow suit and show only rows that match the date you select.

Once selected, you can copy and paste only those rows of data related to the selected date from the first column.

Scott

Posted by Celia on September 06, 0100 9:55 PM

Some other alternatives


Martin
Some other alternatives :-

1. Merge the two workbooks.
Then each day do something like this to retain the data/files you need:
-save the file as the current date
-for sheets that need to be retained in “yesterday’s” file, convert all cells to values
-delete the unwanted sheets from “yesterday’s” file.
Whatever saving/converting/deleting/etc is necessary re the above could be done by a macro.

OR

2.Use a macro to automatically update the refs in the formulas.
Try this :-
On each sheet in your dependant workbook reserve two unused cells (let’s say cells A1 and B1) and do the following :
-format cell A1 as Text
-format cell B1 as Date in the format d-m-yy
-in cell A1, enter the date that is currently used in the formulas on the sheet (i.e. the date being used as the name of the current source file)
-in cell B1 enter the formula =TEXT(NOW(), “d-m-yy”)
The above steps are only required to set things up. You will not have to do them again.

Put the following procedures in their appropriate place in the dependant workbook. Whenever the workbook is opened, the formulas will be automatically updated to link with the file name that uses the current date. Please note that it will only update once at the time it is opened, it will not update again while it remains open.

Private Sub Workbook_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Activate
With ws.Range("B1")
.Value = Now()
.Formula = "=TEXT(NOW(),""d-m-yy"")"
End With
UpdateLinks
Next
End Sub

Sub UpdateLinks()
Dim oldDate As Range, newDate As Range, rngToCheck As Range, cell As Range
Set oldDate = Range("A1")
Set newDate = Range("B1")
Set rngToCheck = ActiveSheet.UsedRange
Application.ScreenUpdating = False
For Each cell In rngToCheck
If cell.HasFormula Then
cell.Replace What:=oldDate.Value, Replacement:=newDate.Value, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
End If
Next
oldDate = newDate
End Sub

If you prefer not to update the links automatically when the dependant workbook is opened, then delete the line “UpdateLinks” from the Workbook_Open macro, change the UpdateLinks macro per below, and run it whenever you want to update.

Sub UpdateLinks()
Dim oldDate As Range, newDate As Range, rngToCheck As Range, cell As Range, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Activate
Set oldDate = Range("A1")
Set newDate = Range("B1")
Set rngToCheck = ActiveSheet.UsedRange
For Each cell In rngToCheck
If cell.HasFormula Then
cell.Replace What:=oldDate.Value, Replacement:=newDate.Value, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
End If
Next cell
oldDate = newDate
Next ws
End Sub

Celia