Expandable Range Command

[eagle670]

I have 6 spreadsheets that all have different ranges of data. I took the largest and wrote a macro hoping that when I applied the macro to the other sheets it would work, but because the data ranges are different, the macro does not perform or capture the data correctly. I was wondering if there is a command that can be inserted into the VB script that would allow the macro to auto expand the range depending on the data content of each spreadsheet.

Thanks

[Franck Knight]

Yes, I had to learn this as well.

The easiest way is by use of the End command in VBA

Please Login or Register  to view this content.

This command will tell you what is the last row in that field.
So depending on how your macro is setup, you can do it in various ways, such as :

Please Login or Register  to view this content.

Running your code on every row using Range("A1").Offset(X,0).
Of course, I don't know what your code is exactly, but hope this gives you a hint. Variable lengts of data is always a problem where I work

[eagle670]

I appreciate the response, but my skill level with VB is limited. I have copied the Macro VB script for you to review. Please indicate as to where the command will need to placed. Thanks!

Sub POG_Refresh_Clnup_1()
'
' POG_Refresh_Clnup_1 Macro
'

'
Sheets("About").Select
ActiveWindow.SelectedSheets.Delete
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1"
Range("D3").Select
Selection.End(xlDown).Select
Range("E24615").Select
ActiveCell.FormulaR1C1 = "x"
Selection.End(xlUp).Select
Selection.Copy
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
ActiveWorkbook.Worksheets("Item Sales").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Item Sales").Sort.SortFields.Add Key:=Range( _
"D2:D24615"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Item Sales").Sort
.SetRange Range("B1:N24615")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
Range("E3").Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("E24615").Select
ActiveCell.FormulaR1C1 = "x"
Selection.End(xlUp).Select
Selection.Copy
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Item Sales").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Item Sales").Sort.SortFields.Add Key:=Range( _
"E2:E24615"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Item Sales").Sort.SortFields.Add Key:=Range( _
"N2:N24615"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Item Sales").Sort.SortFields.Add Key:=Range( _
"F2:F24615"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Item Sales").Sort
.SetRange Range("B1:O24615")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

[Franck Knight]

Okay, I don't know what you're trying to do exactly, but I already saw one major problem.

At the begining of the script, you have a Sheet("About").select. That means everytime you're running the macro, it returns to the sheet "About", instead of staying on the sheet you're on. Otherwise I think your script should be working since it's mostly using End(XlDown) mostly everywhere, thus it shouldn't matter how big your range is on each sheet.

Try removing that line, and run the macro on each sheet again

EDIT : reading through it again.. you have several sheet("Item Sales") mentions in there too... so again, not sure what you'tr tying to do. The "About" sheet seems to be deleted everytime, it might be giving an error message everytime you run it and there is no 'About' sheet. You could try to remove all of the "ActiveWorkbook.Worksheets("Item Sales")" and put "ActiveSheet" instead.

[eagle670]

OK, so the workbook comes with 2 tabs at the bottom. The first is called "Item Sales", the second is called "About". The first step is to completely delete the "About" tab. Then the remainder of the Macro is working inside of the "Item Sales" sheet. I adjusted to "ActiveSheet" as you suggested but, it didn't adjust the "Hard coded" ranges that are still running in the Macro. So for instance:

Range("D3").Select
Selection.End(xlDown).Select
Range("E24615").Select
ActiveCell.FormulaR1C1 = "x"

I insert Column E. I then start at Cell D3, and want to go to the bottom of regardless of Row Count. I then scoot to the right and Put an "X" indicator telling the Macro to stop at this row later in the Macro. The problem is that in the case above, it ALWAYS goes down to E24615, regardless of data in the sheet. Some times it should go to E22000, or E28500, etc.

[Franck Knight]

What confuses me is that nowhere in your script does it seem to be looking for that "X", so writing one doesn't affect anything, so you don't need that part.

Instead, try this then.

I see there are several instances of E24615, which is the last row of your sheet. There is a way around that, which is simple enough.

First, do this :

Please Login or Register  to view this content.

Put that whenever you need to know what's the last row in your sheet. If you add lines (like adding a title), you need to add this line again, just to make sure you know where the last row is everytime.

Next, do a simple Search/Replace.
Search for 24615" (include the " in this case)
Replace by exactly this : " & Lastrow (don't forget the " again)

So it should look like this in the various places it appears :

Please Login or Register  to view this content.

I put underlines to get your attention on the changes you should be seeing. Everywhere the 24615 appears should be replaced by LastRow, which will be the last row of your sheet. It's using variables in this case.

Variables CANNOT be inside Quotes. Instead you need to close the quotes, use & to tie it with LastRow. That's how VBA works.

Hope this helps.