0

I designed a UserForm in VBA that has a RefEdit control that helps users select input range. And for user's convenience, the initial default range in the RefEdit is set as ActiveCell.CurrentRegion. Normally, this works fine.

But there is a defect: if the user selects the whole columns as the address in the RefEdit, e.g., Sheet1!$A:$C, and the data block locates at Sheet1!$A$1:$C$10, my current program will throw an error message letting the user know that there is empty cell in the input range.

But the program should shrink back to Sheet1!$A$1:$C$10 without sending an error message (You may check on Data>Analysis>Data Analysis>Covariance and select some whole columns, then you will understand what I am talking about). Of course, if input range is a block rather than whole columns, my program should not do any shrinkage but should go on the validation and any further computation on that block. How do I accomplish this?

The address is set in this way,

'   Select the current region
    ActiveCell.CurrentRegion.Select
'   Initialize RefEdit control
    UF1.RE1.Text = ActiveWindow.RangeSelection.Address
    UF1.RE1.SetFocus
    UF1.Show
Nicholas
  • 2,560
  • 2
  • 31
  • 58
  • Please share some code. There is no way to know what's going on otherwise. – Kyle Apr 27 '17 at 16:35
  • 1
    Maybe set the area as the intersection of the input range and the used range, or check the size of the input range or whether it has blanks. – SJR Apr 27 '17 at 16:38
  • I just added the code used to initialize the address.@Kyle – Nicholas Apr 27 '17 at 16:39
  • Isn't `used range` a pretty unreliable concept in VBA?@SJR – Nicholas Apr 27 '17 at 16:41
  • I actually just wonder how Excel's Data Analysis able to handle data validation and whole column input both well. – Nicholas Apr 27 '17 at 16:41
  • But I will probably use `Intersect(Selection, ActiveSheet.UsedRange)` if I can't find a better answer@SJR – Nicholas Apr 27 '17 at 16:44
  • Yes `UsedRange` is not perfect, but why can't you just use the `CurrentRegion` - why does the user need to select a range at all? – SJR Apr 27 '17 at 16:45
  • You're right, but even the address in my `RefEdit` is correct, I may come across a user who still changes the input to whole columns after activating my `UserForm` for some reason we don't know. I (my company) just attempt to make this interface robust.@SJR – Nicholas Apr 27 '17 at 16:48
  • Is it possible to do the trick based on http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba? But I don't want to do this if the input range is not whole columns but normal block. Then I guess I need to do some string parsing on the `UF1.RE1.Text` to figure out if the input is whole columns, maybe? – Nicholas Apr 27 '17 at 16:50
  • Honestly, I don't even try to make this even more complicated by showing that the input can be something like `Sheet1!$A:$A,Sheet1!$C:$D` (this is possible if the computation is column-based), which would only make string parsing a big pain!!! – Nicholas Apr 27 '17 at 16:53
  • Maybe do a sense check on the count of the Ref Edit range. – SJR Apr 27 '17 at 16:56
  • Mabe check if the number of rows in the input range is `Rows.Count`, and shrink the array using the trick of [finding last used cell](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) I listed above? Is this what you are suggesting?@SJR – Nicholas Apr 27 '17 at 17:00
  • 1
    Exactly that. If your data are in one contiguous block you can just use `CurrentRegion`. If it could be anywhere, you can use Find to find the last used row and column and your last cell is the intersection of them. – SJR Apr 27 '17 at 17:02

0 Answers0