Select a range (VBA)

JKP Application Development Services.

Microsoft Office Application Development

Up • Defined Names • Corrupt Files • Create Addins • Startup Problems • Chart an Equation • Show Picture • Round2Digits • Control Events • Custom Find • FormulaWrapper • Disable Events • Workbook Open Bug • WebQuery • Register UDFs • Undo With Excel VBA • Select a range (VBA) • Transpose Table • Docking VBE Windows • Excel 2007 Tables • Excel 2007 Tables (VBA) • Update An Addin • Addin Installation • Object Lister • Excel 2007 FileFormat • Catch Paste • Listbox AutoSize

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Getting a range from the user with VBA (Bug in Application.InputBox function)

Applies to

This bug applies to all excel versions as from Excel 5/95. It has been fixed in Excel 2007.

Introduction

This article describes a bug recently discovered by Ron de Bruin and also reported here.

The Application.InputBox function is very useful to get a range from the user. Unfortunately, this function exposes a bug in Excel (all current versions!). If the sheet on which a (range of) cell(s) is selected contains conditional formatting using the : "Formula Is" option, the function may fail, returning an empty range.

How to reproduce the bug

Use this sample code on a worksheet with elaborate conditional formatting (see download below for an example):

Sub ProblemCode()
    Dim oRangeSelected As Range
    On Error Resume Next
    Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
                         "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
        MsgBox "It appears as if you pressed cancel!"
    Else
        MsgBox "You selected: " & oRangeSelected.Address(external:=True)
    End If
End Sub

If you run this code and the user selects a range on a worksheet with conditional formatting which uses a "Formula Is" setting, the code may return an empty range object, even if the user selected a valid area and hit OK. An example formula for the CF might be:

=OR($AL1=1, $AL1=3)

The only reliable workaround is to build a userform to request the range from the user, which I have included as a download here.

Please rate this article:

(Rated: 300 times, average rating: 6.1)

Comments

Showing last 15 comments of 22 in total (Show All Comments):

Comment by: Hassan (8/31/2007 12:46:25 AM)

Hi all

I want to make Macro to copy particular range from different sheets and paste it on the one sheet the clear the content of particular range.

Please help me!

Thanks all

Comment by: Jan Karel Pieterse (8/31/2007 3:04:27 AM)

Hi Hassan,

Like this:

WorkSheets("Sheet1").Range("A1:B10").Copy Destination := _

WorkSheets("Sheet2").Range("C1")

Comment by: Hassan (8/31/2007 3:03:09 PM)

Hi Jan Karel

Thank you for your comment, but what about clearing the content of the particular ranges, by the way it is union range.

Once more thank you very much for your help.

Hassan

Comment by: Jan Karel Pieterse (9/1/2007 10:39:30 AM)

Hi Hassan,

Range("A1").ClearContents

BTW: recording a macro while you do something is a good way to find out what method is used and what arguments are needed.

Comment by: Rich (10/4/2007 11:41:03 AM)

I have a worksheet with data that I'm filtering with autofilter. How can I have the sheet automatically update my formulas (average, mode, median, stdDev, frequency) ranges? They are at the bottom of the worksheet. I would have used subTotal, but it does not allow for mode and median, frequency.

Comment by: Jan Karel Pieterse (10/4/2007 9:22:25 PM)

Hi Rich,

May I advise you to ask this question in one of the newsgroups or fora I link to on my links page?

Comment by: Lennon (11/16/2007 8:08:59 AM)

Hi - I'm trying to modify this VBA code to delete duplicates from range H and L. Currently it only deletes duplicates from H. Thanks

Option Explicit

Sub DeleteDups()

Dim x As Long

Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x

End Sub

Comment by: Jan Karel Pieterse (11/16/2007 8:12:08 AM)

Hi Lennon,

What should it do, should it count a duplicate when both columns are duplicate, or when either of them contains a duplicate?

Comment by: Mahmoud (12/3/2007 12:36:31 PM)

Hi all

help in this issue will be appreciated..

*Select a range of cells according to a criteria..

Thanks

Mahmoud

Comment by: Jan Karel Pieterse (12/4/2007 2:08:09 AM)

Hi Mahmoud,

What criteria do you have in mind?

Comment by: Mahmoud (12/4/2007 12:17:29 PM)

Hi Jan

Data Renewal Automatically every day and i have made an archive for this data by a code....The Request if i take this data once to the archive>>.>.>>>>>....don't take it again if the user push on the macro button twice....I hope that you understand me

Thanks for replay

Mahmoud

Comment by: Jan Karel Pieterse (12/5/2007 2:49:33 AM)

Hi Mahmoud,

I think you would be helped best if you ask your question in one of the newsgroups or forums on internet.

See my links page for some links to newsgroups.

Comment by: Mahmoud (12/5/2007 2:50:21 PM)

Thanks a lot Jan

Best Regards

Mahmoud

Comment by: Craig (2/15/2008 3:23:12 AM)

I love this solution and prior to excel2003, it worked great. My company just upgraded to Excel2003 and the test program produces the error "Object Library Invalid or contains references to object definitions that could not be found". Help! I need this code to work in Excel 2003!

Comment by: Rikkart (2/22/2008 7:58:06 AM)

Great stuff, just finding it, and then the workaround!

I couldn't figure out what was wrong, I'll definitely be back here for my next issue!

Add a comment too!!!

Subscribe in a reader

Getting a range from the user with VBA (Bug in Application.InputBox function)

Applies to

Introduction

How to reproduce the bug

Please rate this article:

Comments

Add a comment too!!!

Use the contact page to issue questions or comments about this website. Copyright © 2003-2007 JKP Application Development Services. _uacct = "UA-1098355-1"; urchinTracker();

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2007 JKP Application Development Services.