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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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,
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top