establishing a range in vba based on cell contents

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
Hi All -

Struggling new VBA-er here!

I have a sorted table that contains 8 columns of data. I want to have vba define a range (A-H) based on the eighth column which could either have a number (all sorted to the top) or a space (sorted at the bottom). I want to go on and make a pivot table of just the range/lines that have numbers in the cells.

Columns

A B C D E F G H
data data data data data data data number or space
data data data data data data data number or space
data data data data data data data number or space
data data data data data data data number or space

Thanks!

Amy
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi,

This code will select just the rows with numbers in - assuming that the data is sorted with the empty cells at the bottom.
Code:
Sub Test()
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A1", .Cells(.Rows.Count, "H").End(xlUp)).Select
    End With
End Sub

Regards,
 

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
Your macro looks like it only defines the whole range - rows with numbers or spaces in Column H.

I want to select only the rows where H is a number.

Row Columns A - G Column H
1 ______data ______number
2 ______data ______number
3 ______data ______number
4 ______data ______space
etc...

In this example I only want the range to be A1 to H3. I need the macro to distinguish between a number and a space and then define the range to only have the numbers (which are always positive and I have sorted to the top).

Amy
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
It does depend on what you mean by "space".

If the cells are empty then it will work. If they contain an actual space character then it won't.

If you can clarify I will take another look.


Regards,
 
Last edited:

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
It does depend on what you mean by "space".

If the cells are empty then it will work. If they contain an actual space character then it won't.

If you can clarify I will take another look.


Regards,

Yup, there is an actual space.
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Does this work for you?
Code:
Sub Test()
    With ThisWorkbook.Worksheets("Sheet1").UsedRange
        .AutoFilter Field:=8, Criteria1:="<>"
        .SpecialCells(xlCellTypeVisible).Select
        .AutoFilter
    End With
End Sub
It does assume that autofiltering is switched off prior to use and I have assumed that you want to select the data.


Regards,
 
Last edited:

Forum statistics

Threads
1,077,674
Messages
5,335,603
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top