Resizing a Range

N

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
 
P

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
 
A

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
 
M

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)
Click to expand...

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)
 
Top