Excel Macro Question

[MartinLiss]

I have a very simple Excel macro consisting of

Rows("271:271").Select
Selection.Copy
Selection.Insert Shift:=xlDown

How can I change it so that it selects the currently selected row rather than row 271?

The currently selected row will always be the row above the first row of some totals, so ideally I'd like the macro to find the total row and do the copy and insert always using the row above it.

[Avatarp]

Set some Range variable and search the Range variable for the type of formating in that Totals row. The count will tell you what row that is. Then you can easyly set the copy and paste ranges.

do you need me to show you how to code this?

You should post this in the VBA forum

[Avatarp]

SelectedRow = ActiveCell.Row
SelectedColumn = ActiveCell.Column

[MartinLiss]

Originally posted by Avatarp
Set some Range variable and search the Range variable for the type of formating in that Totals row. The count will tell you what row that is. Then you can easyly set the copy and paste ranges.

do you need me to show you how to code this?

You should post this in the VBA forum

Yes, thanks.

BTW, I moved the thread.

[Bruce Fox]

Marty,

I find this usefull too:

'Use UsedRange like:
 
    intRowCount = objExcel.ActiveSheet.UsedRange.Rows.Count 'Capture the number of USED Rows
    intColCount = objExcel.ActiveSheet.UsedRange.Columns.Count  'Capture the number of USED Columns

In your case, if no cell is selected, then use

ActiveSheet.UsedRange.Rows.Count [b]- 1[/b]

Bruce.

[MartinLiss]

The first column of my excel spreadsheet looks like this

10
-15
120
-5
Total: 110

The macro I want would atomatically copy the -5 row and insert it under that row so it would look like this.


10
-15
120
-5
-5
Total: 110

I would then manually update the 2nd -5 row.

[opus]

Who is asking?

OK, you want to select the entire row of the actual cell, and paste it into the sheet again (it will be pasted above the actual one, but I think that doesn't matter).

Public Sub Martin()
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Selection.Insert
End Sub

After that you want to manually update the new row(probably putting in correct data) but what about the totals row.
In your example the value of totals didn't change, I guess you want the totals of all cells above (i.e. in your example "105"). If that's correct, EXCEL will do the trick for you.

[Avatarp]

Assign a buttons Macro to the follow sub-routine

Private Sub test()
    ActiveRow = ActiveCell.Row
    ActiveCol = ActiveCell.Column
    
    For i = 1 To 5000
        TotalFindCell = Workbooks("Book1.xls").Sheets("Sheet1").Cells(i, 1)
        If InStr(1, TotalFindCell, "Total") Then
            Workbooks("Book1.xls").Sheets("Sheet1").Cells(i, 1).Select
            ActiveRow = ActiveCell.Row
            Cells(ActiveRow, 1).Select
            Selection.EntireRow.Insert
            Workbooks("Book1.xls").Sheets("Sheet1").Cells(i - 1, 1).Select
            Selection.Copy
            Workbooks("Book1.xls").Sheets("Sheet1").Cells(i, 1).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Exit For
        End If
    Next
    Cells(ActiveRow, ActiveCol).Select
End Sub

[Bruce Fox]

Originally posted by Avatarp
Assign a buttons Macro to the follow sub-routine

     For i = 1 To 5000
        '
        '
    Next

G'Day Avatarp,

There is NO need to pick a random high number to iterate all cells in that row (or column). I posted a method above to take care of the used rows/columns. Dont waste time passing across cells that arn't used!




Bruce.

[dglienna]

Add the Checkmark and the word [RESOLVED] to the subject of the first post in your thread
if your question has been answered satisfactorily.

[MartinLiss]

Thanks for the reminder but actually I've never tested the solutions. I'll do so later today.