1

I'm writing a VBA Add-in for a customer. I tested the code on my computer and a coworker's computer and it executed to perfection. However, when testing on the customer's computer, I get an error on this line

 ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select

From what I've read, this may be due to the fact that .select is used, however, I'm not quite sure what to substitute.

Below is the whole block:

ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2>30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16751204
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 10284031
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

Any ideas as to why this error is occurring? The entire Sub is quite lengthy, but I'd be happy to provide it if that would be helpful.

aLearningLady
  • 1,988
  • 4
  • 24
  • 42
  • FYI - Here's a [pretty good](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) SO thread that outlines ways to avoid `.select`. As for the error, what error is it? Is it always "Application Defined ..." error? – BruceWayne Aug 03 '15 at 14:37
  • Yes it's always the "Application Defined..." error on her system. On our systems, it throws no error whatsoever. Looking into that thread now, thanks! – aLearningLady Aug 03 '15 at 14:41
  • You'd get that error if the `Usedrange` is one row, or the entire sheet. – Rory Aug 03 '15 at 14:42
  • Would it be better to use `CurrentRegion`? – aLearningLady Aug 03 '15 at 14:46
  • Which rows are you intending to select? – Excel Developers Aug 03 '15 at 14:46
  • All used rows below the header row. Perhaps `Range("A2", Selection.End(xlDown))`? – aLearningLady Aug 03 '15 at 14:49
  • Try ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1,1).EntireRow.Select – Excel Developers Aug 03 '15 at 14:52
  • 2
    If the rogue computer has inadvertently created a 'last cell' on the last row of the worksheet then will get this error because you are trying to .Offset before resizing to 1 less row; iow, you can't push the last row off the worksheet. If putting the .Resize before the .Offset fixes this then fix the rogue 'last cell' on the client's worksheet as well and tell him/her not to format cells all the way to the bottom of the worksheet..btw, [Range.CurrentRegion property](https://msdn.microsoft.com/en-us/library/office/ff196678.aspx) is better. –  Aug 03 '15 at 15:31
  • @Jeeped I think you're right - if I'm not mistaken, .xls files have a max capacity of 65,536 rows. While the test data that was provided to me was only ~50,000 rows, perhaps the data she was attempting to use maxed out the capacity for the sheet. – aLearningLady Aug 03 '15 at 15:33
  • If the worksheet is truly maxed out you can avoid this error by resizing before offsetting. However, it is much more likely that she has used a format down to the last row or put a value into the last row and deleted it. The latter is taken care of automatically with Excel 2010 but not so much for earlier versions. –  Aug 03 '15 at 15:38
  • I was just informed that the data she is attempting to manipulate is 462,621 rows long. Will have her reattempt exporting the data to a `.xlsb` (limit of 1,048,576 row I believe) and try again. I'll update soon. – aLearningLady Aug 03 '15 at 15:42

2 Answers2

1

Try this, it works fine in my Excel (2010):

Sub tester()
Dim totalRows As Long
totalRows = ActiveSheet.UsedRange.Rows.Count
With ActiveSheet.UsedRange.Offset(1, 0).Resize(totalRows - 1).Rows

    .FormatConditions.Add Type:=xlExpression, Formula1:="=$G2>30"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority

    With .FormatConditions(1)
        .Font.Color = -16751204
        .Font.TintAndShade = 0
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 10284031
        .Interior.TintAndShade = 0
        .StopIfTrue = True
    End With

End With

End Sub

Also, you can see how to limit/remove the use of .select.

If that still gives you an error, replace totalRows = Activesheet... with totalRows = activesheet.cells(1048576,1).End(xlup).Row Note that this assumes Column A (1 in that range) has data that goes to the last row. If not, choose another column (B = 2, C=3,D=4,etc.).

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Ah, I changed `totalRows` from `Integer` to `Long`. See if that helps now. – BruceWayne Aug 03 '15 at 15:16
  • Hmm now I'm getting `Subscript out of range` error for `.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority` – aLearningLady Aug 03 '15 at 15:27
  • I think the file type was the issue, but I appreciate you showing me how to better avoid using `.select` in my code! – aLearningLady Aug 03 '15 at 15:43
  • 1
    `.xls` files only have 65,536 rows and so you would use `totalRows = Cells(Rows.Count, 1).End(xlUp).Row` to account for varying number of rows in different file formats. – SierraOscar Aug 03 '15 at 15:46
  • @Dr.Trey - i think it might be the file as well, the code I posted is pretty "general", so almost any excel sheet should be okay with it. (Note, I updated it again, I missed a `selection` to touch up). And yeah, it's really good VB practice to limit (if not completely remove) any uses of `.Select` and `.Activate`. – BruceWayne Aug 03 '15 at 15:46
  • @MacroMan is there a resource available that lists the limitations per excel file type? It'd be good to have on hand for instances like these. I typically stick to `.xlsb` files as it's my understanding that excel can open and execute more quickly in binary. – aLearningLady Aug 03 '15 at 15:52
  • 1
    @Dr.Trey [Excel File Formats](https://support.office.com/en-ca/article/File-formats-that-are-supported-in-Excel-a28ae1d3-6d19-4180-9209-5a950d51b719) and [Binary Format Workbooks](https://msdn.microsoft.com/en-us/library/office/gg615597(v=office.14).aspx) - MSDN has all the information you need for file types in Excel. – SierraOscar Aug 03 '15 at 15:57
1

Putting aside the use of ActiveSheet property and .Select for a moment, this:

With ActiveSheet.UsedRange
    .Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select
End With

... is not the same as this:

With ActiveSheet.UsedRange
    .Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1, 0).Rows.Select
End With

If you run into a situation where Excel thinks the .Usedrange extends all the way to the bottom of the worksheet, the former is trying to push its last row off the worksheet and you will generate this error. The latter resizes first so the range to be offset is already one row less and you will not get the error.

While having a worksheet filled to the bottom row is not impossible, it is more likely that it is due to a value that was mistakenly put into the last row then deleted or formatting down to the last row. Neither situation is desirable.

fwiw, I generally prefer the Range.CurrentRegion property instead of the Worksheet.UsedRange property for blocks of data with no 'islands' as rogue 'last cells' are ignored unless there actually is a value in the bottom row..