Resizing a Range

[NetWave128]

This line in my code keeps crashing it says:" Object Variable or Wit
block variable not set"

HERE IS THE LINE:


Dim MyRange as Range

MyRange
ThisWorkbook.Worksheets("Sheet1").Range("a1").CurrentRegion.Resize(MyRange.Rows.Count
1)


Thank you for your help

 

[Paul D]

if you are trying to set a variable to a range object, you have to use set
before the variable name

Set MyRange = ThisWorkbook.Work...

Paul D

 

[Alan Beban]

In the portion of the code "MyRange.Rows.Count", MyRange doesn't refer
to anything. The code can't cause a count of the number of rows in
MyRange because it doesn't yet know what MyRange is.

Alan Beban

 

[Melanie Breden]

This line in my code keeps crashing it says:" Object Variable or With

block variable not set"

HERE IS THE LINE:


Dim MyRange as Range

MyRange =
ThisWorkbook.Worksheets("Sheet1").Range("a1").CurrentRegion.Resize(MyRange.Rows.Count,
1)

try this:

With ThisWorkbook.Worksheets("Sheet1").Range("a1")
Set MyRange = .CurrentRegion.Resize(.CurrentRegion.Rows.Count, 1)
End With

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)