deleting rows in excel vba 1

[beginner999]

how can i delete the rows after pasting the data. Please help me

for example if i pasted
a 1 2 3 4 --- this is the row in excel with 5 cells
b 7 8 9 1 --- 2nd row
c 1 1 1 1 --- 3rd row

then I need to delete first row and then i paste same range

a 1 2 3 4 --- this is the row in excel with 5 cells
b 7 8 9 1 --- 2nd row
c 1 1 1 1 --- 3rd row
empty row
b 7 8 9 1 --- 2nd row
c 1 1 1 1 --- 3rd row
empty row
a 1 2 3 4 --- this is the row in excel with 5 cells
b 7 8 9 1 --- 2nd row
c 1 1 1 1

here i need to delete 2 rows from what ever i pasted. then finally it looks like this

a 1 2 3 4 --- this is the row in excel with 5 cells
b 7 8 9 1 --- 2nd row
c 1 1 1 1 --- 3rd row
empty row
b 7 8 9 1 --- 2nd row
c 1 1 1 1 --- 3rd row
empty row
c 1 1 1 1 --- 3rd row

so far my code is like this

r = ActiveCell.CurrentRegion.Rows.Count
lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

UsedRange.Select
Selection.Copy

For i = 1 To r
ActiveCell.Offset(lastrow + 1, 0).Select
ActiveSheet.Paste

Next i

 

[SkipVought]

"how can i delete the rows after pasting the data. Please help me"

Have you tried doing this on the sheet with the macro recorder on?

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[beginner999]

This is how i have done

ActiveSheet.Paste
Selection.Rows(i).Delete

 

[SkipVought]

That is NOT macro recorded code.

Need to see ALL selections that you make. Do not begin with pre-selected ranges.

Try again!

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[beginner999]

This is macro recorde code for my purpose for this input

a 1 2 3
b 2 3 4
c 3 4 5
d 4 5 6

this iam going to make it automated for n number of rows using vba

Sub Macro1()
'
' Macro1 Macro

'

'
Range("A14").Select
Selection.Copy
Range("A6").Select
ActiveSheet.Paste
Range("A66").Select
Application.CutCopyMode = False
Selection.ClearContents
Rows("6:6").Select
Selection.Delete Shift:=xlUp
Range("A14").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Range("A1011").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A14").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Range("A1315").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A13").Select
End Sub

 

[AnotherHiggins]

beginner999,

Having followed the other thread, I'd like to suggest that perhaps you are too caught up in the baby steps of what you're trying to do. Not seeing the forest for the trees. "In the weeds".

Back up and explain what it is you're trying to do. Not "autofill this" or "delete that". But your overall objective. The "30000 foot view".

You said in the other thread that, "empty row between 'sections' may be helpful in copying the range right". If you're going to use a macro, you don't need to have the empty rows at all. You think you do because, again, you are caught up in trying to use autofill. So if you don't NEED empty rows between sections, just let us know.

So I'll ask this one more time: Please explain what it is you're trying to do.
- What does your source data look like?
- What will the final outcome look like after your dream macro has run? (you still haven't provided this)
- What is the business case for this?


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Can you explain the business case for this exersize? If I understood that, there might be a simpler solution.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[beginner999]

this is my input

a 1 2
b 5 4
c 5 8
d 6 7
e 5 3
this is my next step

b 5 4 a 1 2
c 5 8 a 1 2
d 6 7 a 1 2
e 5 3 a 1 2

c 5 8 b 5 4
d 6 7 b 5 4
e 5 3 b 5 4

d 6 7 c 5 8
e 5 3 c 5 8

e 5 3 d 6 7


After generating this I calculate mean for each row.

Business case is to calculate statistical means.

 

[beginner999]

Please help me and I appreciate your help

 

[nath]

Hello Beginner999,

First of all if you have a whole load of rows I wouldn't advise you to create your data on the same sheet replacing cells etc. Keep your initial data on one sheet. Create the result on another one.

Then indeed forget the autofill and go into programming without selecting cells and so on. The program should go more or less as follows:

'I assume the data sheet is called Source, the result sheet is called Result
'I assume the first row of data is in cell A1
'I assume the data are not separated by a row (using end function to find last data)
'I assume there is always the same number of columns
Sub mysub()
Dim mySourceSht As Worksheet, myResultsht As Worksheet
Dim numData As Long, i As Long, j As Long, curRow As Long 'don't know how many data you have so declare them as long in case >32000
Dim k As Integer, numCol As Integer

Set mySourceSht = ThisWorkbook.Sheets("Source")
Set myResultsht = ThisWorkbook.Sheets("Result")
curRow = 1 'corresponds to the row where results must be writen

numData = mySourceSht.Range("A1").End(xlDown).Row
numCol = mySourceSht.Range("A1").End(xlToRight).Column

For i = 1 To numData
For j = i + 1 To numData
For k = 1 To numCol
myResultsht.Cells(curRow, k).Value = mySourceSht.Cells(j, k).Value
myResultsht.Cells(curRow, k + numCol).Value = mySourceSht.Cells(i, k).Value
Next k
curRow = curRow + 1
Next j
curRow = curRow + 1 'put this line only if you want a space between your groups
Next i
End Sub

let me know how it goes.

nate