Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

select a set of data in a column

Hi folks. I want to select a range of cells that are in a column. I remember that there is a good way to do this in such a way that if there are some missing data in some cells, that I can still capture the whole set of data. What I have would not do that. Can someone tell me a good way?
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select

Open in new window

0
willie108
Asked:
willie108
  • 3
  • 2
2 Solutions
 
SteveCommented:
This one liner should select all of the data in coumn A from A1 to the last row of Data.

Range("A1", "A" & Range("A" & Rows.Count).End(xlUp).Row).Select

Open in new window

0
 
SteveCommented:
If programming in VBA, it is (in most cases) best to work with the range rather than select it.

So maybe using something like:

Dim MyRange as Range
Set MyRange = Range("A1", "A" & Range("A" & Rows.Count).End(xlUp).Row)
'Code to do something to the range, examples:
With MyRange
.copy
.ClearContents
End with

For each c in MyRange.Cells
c.value = "Row no. " & C.row
next

Open in new window

0
 
willie108Author Commented:
Thanks. So would this be the recommended way to get to the cell just below that?
    Range("A1").Select
    Range("A1", "A" & Range("A" & Rows.Count).End(xlUp).Row).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select

Open in new window

0
 
SteveCommented:
This should select the cell with no data at the end of the column.

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

Open in new window

0
 
willie108Author Commented:
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now