Problem with Range selection macro

[floridagunner]

Hello,

I am running the following piece of code in my macro.

Sub SelectData1()

Set mySelection = Range(ActiveCell, ActiveCell.End(xlDown))
mySelection.Select

End Sub

When I run the above code as a stand alone macro it always works as it should; ie. it selects all the data in the column below the active cell.

However when I'm running it as part of a larger macro instead of selecting the data below the activecell in column R it jumps to selecting all the data in column A.

Anybody have any ideas on how this can be fixed?

 

[wigi]

What is the ActiveCell at that moment, in that "larger macro".

Did you know that you do not need to Select in VBA?
You can do away with a lot of code, and speed it up while removing the flickering on screen.

 

[floridagunner]

If I delete the line of code below; the active cell is R2.

myselection.select

However when I add that code, instead of selecting the data immediately below that cell, it jumps to column A and selects the activerange in that column instead.

Incidentally, I have found a different workaround solution for this particular piece of data which is as follows:

Set eRange = myRange.Find(What:="Period").Offset(1, 0)
Set eRange = Range(eRange, eRange.End(xlDown))
eRange.Select

However I'm still curious to know why the original line of code does not seem to work in this macro, but works as a stand alone macro. It would cut down on a lot of work for me on this data if I could find a way to utilize the mySelection.Select line of code.

 

[Norie]

If you know the columnm, eg R, you want to select/delete the range in you could specify it in the code:

Set eRange = Range("R2")

Range(eRange, Cells(Rows.Count, eRange.Column).End(xlUp)).Delete

If you don't know the column but do know the header then using Find as you have is a good idea.

As for why the original code didn't work, it's probably because the ActiveCell wasn't in column R.

That's quite likely if you have code executed before this code that uses a lot of activating/selecting etc.

Hard to tell for definite without seeing the earlier code though.

 

[mirabeau]

Hello,

I am running the following piece of code in my macro.

Sub SelectData1()
MsgBox ActiveCell.Address & "   " & Activecell.End(xldown).Address
Set mySelection = Range(ActiveCell, ActiveCell.End(xlDown))
mySelection.Select

End Sub

When I run the above code as a stand alone macro it always works as it should; ie. it selects all the data in the column below the active cell.

However when I'm running it as part of a larger macro instead of selecting the data below the activecell in column R it jumps to selecting all the data in column A.

Anybody have any ideas on how this can be fixed?

Include the red line above in your code to see what you're actually telling the code to select, and if it differs from what you want to select.

 

[floridagunner]

Hello Norie, below is the code in it's entirety:

Sub ProcessSAPOutput()

'Define variables myRange and myCell as ranges.

Dim myRange As Range
Dim cRange As Range
Dim dRange As Range
Dim myCell As Range
Dim mySelection As Range


'Delete sheet2 and sheet3. Create the SAP Raw Data and SAP Refined Data sheets, and remove grouping on the latter.
'Autofit all columns.

Sheets(Array("Sheet2", "Sheet3")).Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "SAP Raw Data"
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "SAP Refined Data"
Sheets("SAP Refined Data").Select
Cells.Select
Selection.Rows.Ungroup
Selection.Rows.Ungroup
Selection.Font.Name = "Calibri"
Selection.Columns.AutoFit

'Delete columns G and H, then delete all rows with blank values in column A.

Range("G:H").Delete
Range("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Name/Define row 1 on SAP Refined Data sheet as "myRange" and format myRange. Also define the cRange, myCell and
'myField ranges.

Set myRange = Sheets("SAP Refined Data").Range("1:1")

Set cRange = myRange.Find(What:="Cost Element").Offset(1, 0)
Set cRange = Range(cRange, cRange.End(xlDown))
Set myCell = myRange.Find(What:="Dr/Cr indicator")
Set dRange = myCell.Offset(1, 0)
Set dRange = Range(dRange, dRange.End(xlDown))
Set eRange = myRange.Find(What:="Period").Offset(1, 0)

Set SourceData = Sheets("SAP Refined Data").UsedRange
Set mySelection = Range(ActiveCell, ActiveCell.End(xlDown))
myField = myCell.Column

myRange.Select
Selection.Font.Bold = True
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
End With

'Convert values in column D to a number format.

Range("A65536").Value = "1"
Range("A65536").Copy
cRange.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
Rows("65536:65536").Delete

'Rename the header in cell B1 to "Segment".
Range("B1").Value = "Segment"

'Delete the settlement rows.

myRange.AutoFilter
myCell.Select
Selection.AutoFilter Field:=myField, Criteria1:="O"
dRange.Rows.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
myRange.AutoFilter
dRange.Select
myRange.Select
Selection.Find(What:="Segment").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Formula = "=IF(S2=""D.02642"",""Legacy"",IF(S2=""M.00453"",""SMALL COMMERCIAL"",""RESIDENTIAL""))"
ActiveCell.EntireColumn.Select
Selection.Columns.AutoFit
myRange.Select
Selection.Find(What:="CO Area Currency").Select
Selection.Value = "Cost Category"
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Formula = "=IF(OR(LEFT(C2,11)=""D.02642.1.5"",MID(C2,9,1)=""3""),""INCENTIVES"",IF(LEFT(C2,7)=""D.02642"",""ADMINISTRATIVE COST"",IF(MID(C2,9,1)=""2"",""ADMINISTRATIVE COST"",IF(MID(C2,9,1)=""1"",""CAPITAL""))))"
Selection.Columns.AutoFit

eRange.Select
mySelection.Select

End Sub

Hopefully it won't be too hard too make sense of it all, as I have not yet added comments where I should have.

Thanks

 

[floridagunner]

Hello Mirabeau,

I just added your line of code above and the message box returned the cell address R2 and the end address as R32284, which is correct. However as soon as I pressed the ok button on the message box, the selection jumped to the active range on column A.

 

[Norie]

Well I can't see anything obvious and you don't appear to be using ActiveCell.

As I thought though there's a whole lot of activating/selecting going on - so much it made my head dizzier trying to follow it.

You should try and avoid using Activate/Select.

Actually I've just noticed you are using ActiveCell, you really should avoid that as that was the problem with your original code.

Use something like I posted, or the code you posted.

 

[floridagunner]

What's wrong with the ActiveCell function? Is it unstable?

 

[floridagunner]

Wigi, when you say that it is not necessary to use the Select function, do you mean that there is a better way to navigate to a range?