How can I change the position of the active cell (or raise the value with one) in Excel

Question

After performing some formatting and date manipulation actions in a macro, I want to remove the extra rows below my list.

I've come with the following solution, but that deletes the last line but I need to delete everything below the last line...

Code:

'Delete garbage at the bottom:
1)    Range("A1").Select
2)    Selection.End(xlDown).Select
3)    Range(Selection, Selection.End(xlDown)).Select
4)    Range(Selection, Selection.End(xlDown)).Select
5)    Range(Selection, Selection.End(xlToRight)).Select
6)    Range(Selection, Selection.End(xlToRight)).Select
7)    Selection.Delete Shift:=xlUp

I created this by recording a macro. What is need is to lower the activecell one row after step 2. With step 2 I place my activecell ON the last line.

I thought about the following solution (but I don't know how to do this)

1) After step 2. Read out the current position of the Active cell. (Selection.Address) and put in in a variable and raise it with one. 2) Put cursor on the new position (Range("variable").Select 3) Do the rest of the magic.

Anyone knows a good solution?

UPDATE:

To answer my question:

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

This will change to position of the active cell with one row.

But I understand that using this method is unwanted, thanks for the answers.

My complete Macro looks like this:

Sub Function1()
'
' Record Macro

'Split one column in multiple cells
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(20, 1), Array(30, 4), Array(36, 1), _
        Array(45, 4), Array(51, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(67, 1)), _
        TrailingMinusNumbers:=True
'Remove empty / uneeded Columns
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
'Formatting Cells
    Columns("E:E").Select
    Selection.NumberFormat = "#,##0.00"
'Insert Formula #1 on row 1
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/100"
'Autofill formula to row 5000 (There will never be more than 5000 lines)
    Selection.AutoFill Destination:=Range("J1:J5000")
    Range("K1").Select
'Insert Formula #1 on row 1
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""+"",RC[-1],RC[-1]*-1)"
    Range("K1").Select
'Autofill formula to row 5000 (There will never be more than 5000 lines)
    Selection.AutoFill Destination:=Range("K1:K5000")
'Colomn K, copy/paste as Value
    Columns("K:K").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("K:K").Select
'Remove Columns that are not needed anymore
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:G").Select
    Selection.Delete Shift:=xlToLeft
'Column 2 Search for "/" and remove it
    Columns("B:B").Select
    Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'Remove the unneeded Autofill lines
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
'Reset view
    Cells.Select
    Cells.EntireColumn.AutoFit

End Sub

I know this is unwanted, but by using the Macro recording function I was able to automate some tasks and save a lot of time!! I am very curious how this code can be optimized. If someone have some ideas, please let me know!

Answer 1

I'm bit of a novice to vba and Stackoverflow myself, but I suppose you could try to mix selection.offset with the worksheet.UsedRange and ClearContents like so:

'select A1
ActiveSheet.Range("A1").select

'use Selection.End(xlDown).Offset(row,column) to provide the first range argument
'and "A" & worksheet.usedrange.rows.count for the second range argument
ActiveSheet.Range(Selection.End(xlDown).Offset(1, 0), _ 
"A" & ActiveSheet.UsedRange.Rows.Count).EntireRow.ClearContents

I am sure someone can supply you with a more generic solution, but I hope some of this could be of use for this particular issue of yours

Answer 2

I assume you have a blank line between the rows you want to keep and the rows you want to delete. If so try:

  Dim RowLastKeep As Long
  Dim RowLastDelete As Long

  ' It is rarely a good idea to assume the active worksheet is the one
  ' required.  Replace "Sheet1" with the name of your worksheet
  With Worksheets("Sheet1")

    RowLastKeep = .Cells(1, "A").End(xlDown).Row
    RowLastDelete = .Cells(Rows.Count, "A").End(xlUp).Row

    .Rows(RowLastKeep + 1 & ":" & RowLastDelete).EntireRow.Delete

  End With

Explanation

There are a number of questions with answers that explain why it is a bad idea to use Select. For me, the biggest reason is that I find code like yours confusing.

They are special cases when working with the Active Worksheet is appropriate but they are rare. I have seen code fail because the user was looking at the wrong worksheet when the macro was started. I always use the With statement so my code is explicit about which worksheet is its target. If you do this you must replace Range("A1") which references the active worksheet with .Range("A1") which references the worksheet defined by the With statement. Remembering to place all the dots in front of every Range, Cells, etc. takes practice but the end result is much clearer code.

I hope you agree that using End but getting the row number, rather than selecting a new active cell, gives clearer code.

It may not matter now if the code is clear because you remember what you are attempting to achieve. But when you want to update this macro in six months, clear code is a life saver.