0

I have a question,

I have a table with data:

    A       B       C
   Type   Height Length
    1       2       2
    2       3       9
    3       8       2
            2       3
    1               0
                    1

I've been reading how to select that range but the problem I can't solve is how to select the range above until the row where at least one column contains a numeric value. But the problem is that the range is a part of a reference and Excel assumes that the number of used rows is larger because a lot of rows are blank and are part of a reference.

So my question is:

How do I select the above range until the first row where none of columns A, B or C contain at least one numeric entry, and where the number of used rows is larger because the range is a part of reference.

I tried

Sub Choose()
Dim Lastrow As Integer
Lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
MsgBox Lastrow
'Range("A2:C" & Lastrow).Select
End Sub

But the MsgBox returns 118, which is because the Range I want to select is part of a reference...

Does anyone have an idea?

EDIT: That does not work, still select empty cells because it is reference...

Sub Sort3()
Dim oneRange As Range
Dim aCell As Range
Worksheets("Analys").Select
Set oneRange = ActiveCell.CurrentRegion.Range("D1")
Set aCell = Range("M10")

oneRange.Sort Key1:=aCell, Order1:=xlDescending, Header:=xlYes
End Sub
Community
  • 1
  • 1
user1665355
  • 3,324
  • 8
  • 44
  • 84
  • since you want to find `first row where none of columns A, B or C contain at least one numeric entry` simpliest solution would be to use loop. If you want single line solution, you can use `Lastrow = Evaluate("=MATCH("""",A:A&B:B&C:C,0)")` but it may be slow. This one faster: `Lastrow = Evaluate("=MATCH("""",A1:A1000&B1:B1000&C1:C1000,0)")` but you should be sure that `1000` (or any other number) is greater than lastrow – Dmitry Pavliv Mar 26 '14 at 12:17
  • @simoco Thanks, but it still selects to few number of rows.... 110 while it should select 118... – user1665355 Mar 26 '14 at 12:27

1 Answers1

1

If you have "ragged" columns like:

sample

and want a range to get "all the data", then:

Sub GetTheBlock()
    Range("A1").CurrentRegion.Select
End Sub

will select A1:C7

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • @Gary's Student That is not the problem. The problem is that your solution will select to many rows because the current region is a reference where a lot of rows are blank. The columns are "ragged" but I dont want to select any row where there are no entries. The last row I want to select will have at least one data entry (character or not) in some column :) – user1665355 Mar 26 '14 at 12:34
  • @user1665355: **Use simoco's suggested fix to my post** .......don't worry about including blank rows -> they will border **CurrentRegion** – Gary's Student Mar 26 '14 at 12:37
  • Hm ok... I then want to sort the selected region based on one column.. Would it work? – user1665355 Mar 26 '14 at 12:39
  • @user1665355: Perform some experiments on dummy data........let us know what happens! – Gary's Student Mar 26 '14 at 12:42
  • Still not working though...When I am trying to sort the current region select the empty cells because they are reference... See my Edit pls:) – user1665355 Mar 26 '14 at 15:44