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