selecting range variable

hey guys,

i've got a function that is accepting a range argument. now i'm trying to select that range cell like range("A1").select action so that i can do offsets, etc and it would keep state by using selection.offset(1,0) etc.

how do i select a cell in excel by using this range variable?

I've tried:

rngStartingCell.select - and it returns an error
range(rngstartingcell).select - and it returns an error
sheets("graphs").rngstartingcell.select - and it returns an error

Function ClearCells(ByRef rngStartingCell As Range) --> this is the function definition

thanks guys in advance!!! = ))
developingprogrammerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
You can't select a cell if it's sheet isn't active, but then you don't need to. Try this version:

Function ClearCells(ByRef rngStartingCell As Range)
   Dim rngEndCell             As Range
   With rngStartingCell
      If .Value = "" Then Exit Function
      'this is the line that has a problem
      If .Offset(, 1).Value <> "" Then
         Set rngEndCell = .End(xlToRight)
      Else
         Set rngEndCell = .MergeArea
      End If
   End With
   With rngEndCell
      If .Offset(1).Value <> "" Then
         Set rngEndCell = .End(xlDown).MergeArea
      End If
   End With
   Range(rngStartingCell, rngEndCell).ClearContents
End Function

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
How do you assign the range to the variable?
0
 
Michael FowlerSolutions ConsultantCommented:
It would best if you post the workbook for us to look at. If not then as ssaqibh noted please advise how the range is assigned as the error is likely there

Michael
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
developingprogrammerAuthor Commented:
thanks guys, here's the workbook.

kindly search for comment "'this is the line that has a problem" and put a breakpoint on the following line that contains "rngStartingCell.Select", then just run UpdateVMS().

i'm not sure why it can't work.

i've tried this same way of assigning the variable and then selecting the cell through 2 layers of functions and it works. not sure why this one can't work = (
testing.xls
0
 
Michael FowlerSolutions ConsultantCommented:
Your problem here is the merged cells. The easiest way to solve this would be to not use the merges

Michael
0
 
Michael FowlerSolutions ConsultantCommented:
Sorry another thing, this method has the additional advantage that the workbook cannot be accessed if macros are disabled. In the past I have set up the Unlock page with a warning that macros must be enabled and information for the user on how to enable macros.

Michael
0
 
developingprogrammerAuthor Commented:
guys thanks for all your help! it was indeed the sheet not being selected = )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.