-1

I need to select the last cell in my specified range.

I am pulling data from a database. I successfully go to the right sheet, and find & select the variable number of managers (starting two rows down from "Number of Managers". Now I just need to select the last line of the active cells and run my If statement below.

Windows("MCS Cumulative_Data.xls").Activate
Sheets("Sheet 12").Select
Range("A1").Select
Cells.Find(What:="Number of Managers", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select

' Part of Code, but not Important to question.

ActiveCell.Offset(0, 1).Select
If ActiveCell.Value = "" Then
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"  
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "0"
Else
End If

I've tried about 15 different Dim statements from other questions. They all seem like be close to asking what I am, but not exactly.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Cj17digr
  • 31
  • 5
  • 2
    This isn't clear. What does `Now I just need to select the last line of the active cells and run my If statement below.` have to do with `I've tried about 15 different Dim statements from other questions. They all seem like be close to asking what I am, but not exactly.` What is your question? – Cindy Meister Oct 14 '19 at 16:53
  • 1
    The *very first thing you should do* is learn how to [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). As far as finding the last row in a range, it really depends on the nature of the range. If your rows have blanks and there is no other data in rows below the data you want, you can use `Cells(Rows.Count,1).End(xlUp).Row` (be sure to explicitly tie all range objects to a worksheet. – Scott Holtzman Oct 14 '19 at 17:06
  • @CindyMeister after Range(ActiveCell, ActiveCell.End(xlDown)).Select I now have a range selected. I need to now select the last cell in this highlighted range. How do I select (make my active cell) the last cell in a range? – Cj17digr Oct 14 '19 at 17:06
  • @Cj17digr - `ActiveCell.End(xlDown).Select` will select the last cell in the range, but *please* heed my warning and learn right away to not use select. (there a .001% of cases where it is needed, but this is not one of them). You'll be glad you did because you will avoid tons of debugging hours in your code. – Scott Holtzman Oct 14 '19 at 17:08
  • Thanks Scott Holtzman! That worked. I'll try to learn to avoid select in the future as well. – Cj17digr Oct 14 '19 at 17:17

3 Answers3

2

a possible example of "how to avoid select" (and some other little tips), would "collapse" your code to the following:

With Workbooks("MCS Cumulative_Data.xls").Sheets("Sheet 12")
    With .Cells.Find(What:="Number of Managers", After:=.Cells(1, 1), _
                     LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(2, 0).End(xlDown).Offset(0, 1)            
        If .Value = vbNullString Then .Resize(1, 13).Value = "0"                        
    End With
End With
DisplayName
  • 13,283
  • 2
  • 11
  • 19
  • So, this is a part of a much larger code. This part of the code I need to ensure the first and last manager (asset manager) set of returns are there. If they are blank my other code doesn't grab them. I am verifying 4 columns. 1 column 2 rows after "number of Managers". Then the last manager in that string of managers. Then after that "last manager" there will be more 2 rows down from that. I verify that manager's data and then the last one in that row. Not needed, Manager data filled in from the manager themselves are the first group, the second group is custom data created by me – Cj17digr Oct 14 '19 at 17:26
  • In any case your whole posted code reduces to what I posted. And you can extend it to whatever your need as long as you try and understand it, possibly by reading what Scott Holtzmann suggested you – DisplayName Oct 14 '19 at 17:35
  • Thanks. I'm reading the link Scott shared. I'm trying to understand. – Cj17digr Oct 14 '19 at 17:41
  • Your code doesn't work. It doesn't do what my code above does. Sorry I'm trying to use it as a bridge to understand the avoid select examples. – Cj17digr Oct 14 '19 at 17:58
1

How about this:

    lastrow = Range(ActiveCell, ActiveCell.End(xlDown)).Cells.Count 'gets the last row from active cell
    NumRow = ActiveCell.Row 'gets the colum the active cell is in
    Cells(lastrow, NumRow).Select 'selects that cell
SpyingSphinx
  • 78
  • 7
1

ActiveCell.End(xlDown).Select

Does not select the last sell in a selected group, but does grab the cell I need.

Cj17digr
  • 31
  • 5