Counting blanks in range using VBA Offset

[ExcelMonkey]

I know how to define a range using Excel's Offset function (OFFSET(Rows,
Columns, Height, Width))

But how do I do it using the VBA Offset function. It only includes
(OFFSET(Rows, Columns)).

I am trying to count the blank cells betwee rows 2 and rows 65236 on a
sheet. Row 2 is named PasteStartCell. I think the use below does not define
a range between row 2 and row 65536. It simply defines row 65536 as I am
offsetting row 2 by 65535 additional rows. How do I make it refer to the
range itself. Note I do not what to select the range and it cannot include
row 1

Numblanks =
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0)))

Thanks

[K Dales]

Try this (immediate pane) to see how it works:
? Range("A1:B2").Offset(3,3).Range(Cells(1,1),Cells(5,5)).Address
--
- K Dales


"ExcelMonkey" wrote:

> I know how to define a range using Excel's Offset function (OFFSET(Rows,
> Columns, Height, Width))
>
> But how do I do it using the VBA Offset function. It only includes
> (OFFSET(Rows, Columns)).
>
> I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> sheet. Row 2 is named PasteStartCell. I think the use below does not define
> a range between row 2 and row 65536. It simply defines row 65536 as I am
> offsetting row 2 by 65535 additional rows. How do I make it refer to the
> range itself. Note I do not what to select the range and it cannot include
> row 1
>
> Numblanks =
> Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0)))
>
> Thanks

[Tom Ogilvy]

You can't offset 65535 rows from row 2 since offset is zero based.

? range("A2").Offset(65534,0).Address
$A$65536


What you want is resize

Numblanks =
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Resize(65535,1
)))

--
Regards,
Tom Ogilvy


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I know how to define a range using Excel's Offset function (OFFSET(Rows,
> Columns, Height, Width))
>
> But how do I do it using the VBA Offset function. It only includes
> (OFFSET(Rows, Columns)).
>
> I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> sheet. Row 2 is named PasteStartCell. I think the use below does not
define
> a range between row 2 and row 65536. It simply defines row 65536 as I am
> offsetting row 2 by 65535 additional rows. How do I make it refer to the
> range itself. Note I do not what to select the range and it cannot
include
> row 1
>
> Numblanks =
>
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0
)))
>
> Thanks

[Bob Phillips]

Range("A1").Resize(rows,columns).Name = "myRange"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I know how to define a range using Excel's Offset function (OFFSET(Rows,
> Columns, Height, Width))
>
> But how do I do it using the VBA Offset function. It only includes
> (OFFSET(Rows, Columns)).
>
> I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> sheet. Row 2 is named PasteStartCell. I think the use below does not
define
> a range between row 2 and row 65536. It simply defines row 65536 as I am
> offsetting row 2 by 65535 additional rows. How do I make it refer to the
> range itself. Note I do not what to select the range and it cannot
include
> row 1
>
> Numblanks =
>
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0
)))
>
> Thanks

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I know how to define a range using Excel's Offset function (OFFSET(Rows,
> Columns, Height, Width))
>
> But how do I do it using the VBA Offset function. It only includes
> (OFFSET(Rows, Columns)).
>
> I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> sheet. Row 2 is named PasteStartCell. I think the use below does not
define
> a range between row 2 and row 65536. It simply defines row 65536 as I am
> offsetting row 2 by 65535 additional rows. How do I make it refer to the
> range itself. Note I do not what to select the range and it cannot
include
> row 1
>
> Numblanks =
>
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0
)))
>
> Thanks

[ExcelMonkey]

But does resize permanently change the range behind the name PastCellStart?
I use this name elsewhere in my code and do not want to change the range
definition behind that name. If it does, I will not be able to use it.



"Tom Ogilvy" wrote:

> You can't offset 65535 rows from row 2 since offset is zero based.
>
> ? range("A2").Offset(65534,0).Address
> $A$65536
>
>
> What you want is resize
>
> Numblanks =
> Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Resize(65535,1
> )))
>
> --
> Regards,
> Tom Ogilvy
>
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> > I know how to define a range using Excel's Offset function (OFFSET(Rows,
> > Columns, Height, Width))
> >
> > But how do I do it using the VBA Offset function. It only includes
> > (OFFSET(Rows, Columns)).
> >
> > I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> > sheet. Row 2 is named PasteStartCell. I think the use below does not
> define
> > a range between row 2 and row 65536. It simply defines row 65536 as I am
> > offsetting row 2 by 65535 additional rows. How do I make it refer to the
> > range itself. Note I do not what to select the range and it cannot
> include
> > row 1
> >
> > Numblanks =
> >
> Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0
> )))
> >
> > Thanks
>
>
>

[ExcelMonkey]

So is what I really want:

Application.WorksheetFunction.CountBlank(Workbooks(AuditShtName).Range("B2:B65536"))

I think I was getting obsessed with using the named range itself. The range
is not that meaningful except that it exists in the first row of the column I
want to count blanks in. As I do not want to include the first row anyway, I
guess it means I should just not use that named cell as a point of reference.

"ExcelMonkey" wrote:

> But does resize permanently change the range behind the name PastCellStart?
> I use this name elsewhere in my code and do not want to change the range
> definition behind that name. If it does, I will not be able to use it.
>
>
>
> "Tom Ogilvy" wrote:
>
> > You can't offset 65535 rows from row 2 since offset is zero based.
> >
> > ? range("A2").Offset(65534,0).Address
> > $A$65536
> >
> >
> > What you want is resize
> >
> > Numblanks =
> > Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Resize(65535,1
> > )))
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > I know how to define a range using Excel's Offset function (OFFSET(Rows,
> > > Columns, Height, Width))
> > >
> > > But how do I do it using the VBA Offset function. It only includes
> > > (OFFSET(Rows, Columns)).
> > >
> > > I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> > > sheet. Row 2 is named PasteStartCell. I think the use below does not
> > define
> > > a range between row 2 and row 65536. It simply defines row 65536 as I am
> > > offsetting row 2 by 65535 additional rows. How do I make it refer to the
> > > range itself. Note I do not what to select the range and it cannot
> > include
> > > row 1
> > >
> > > Numblanks =
> > >
> > Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0
> > )))
> > >
> > > Thanks
> >
> >
> >

[Tom Ogilvy]

No, resize does not do that. If you have done

Insert=>Name=>Define
Name: = PasteStartCell
Refersto: whatever

then this will not change the definition of PasteStartCell - it will just
use it as an anchor:

Numblanks =
Application.WorksheetFunction.CountBlank(Range("PasteStartCell").Offset(6553
5,1))

would be a single column from row 2 to row 65536

if you want the entire row

Numblanks =
Application.WorksheetFunction.CountBlank(Range("PasteStartCell" _
).Offset(65535,1).EntireRow)





"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> But does resize permanently change the range behind the name
PastCellStart?
> I use this name elsewhere in my code and do not want to change the range
> definition behind that name. If it does, I will not be able to use it.
>
>
>
> "Tom Ogilvy" wrote:
>
> > You can't offset 65535 rows from row 2 since offset is zero based.
> >
> > ? range("A2").Offset(65534,0).Address
> > $A$65536
> >
> >
> > What you want is resize
> >
> > Numblanks =
> >
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Resize(65535,1
> > )))
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > I know how to define a range using Excel's Offset function
(OFFSET(Rows,
> > > Columns, Height, Width))
> > >
> > > But how do I do it using the VBA Offset function. It only includes
> > > (OFFSET(Rows, Columns)).
> > >
> > > I am trying to count the blank cells betwee rows 2 and rows 65236 on a
> > > sheet. Row 2 is named PasteStartCell. I think the use below does not
> > define
> > > a range between row 2 and row 65536. It simply defines row 65536 as I
am
> > > offsetting row 2 by 65535 additional rows. How do I make it refer to
the
> > > range itself. Note I do not what to select the range and it cannot
> > include
> > > row 1
> > >
> > > Numblanks =
> > >
> >
Application.WorksheetFunction.CountBlank(Range(PasteStartCell.Offset(65535,0
> > )))
> > >
> > > Thanks
> >
> >
> >