Gaps in pasted rows

[Lavalips]

Hi there,

I hope that you can help me.

I am wishing to copy and paste rows from a donor worksheet to a recipient worksheet based on one of 2 criteria being met in column H.

When the appropriate rows are copied across there are gaps in the recipient worksheet pertaining to the questions that didn't meet the criteria in column H.

e.g i want to copy rows A10, A23, A24, A98..... on the recipient worksheet the rows have been pasted over fine but there are massive blank rows inbetween them.

If someone could possibly help me in making sure the pasted rows on the recipient worksheet follow directly below each other without any gaps, that would be amazing.

here is the macro I am using.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
Dim rng As Range
If Not Intersect(Range("H10:H" & Rows.Count), Target) Is Nothing Then
Set wsh = Worksheets("NonComformitySchedule")
For Each rng In Intersect(Range("H10:H" & Rows.Count), Target)
Select Case rng.Value
Case "M", "R"
rng.EntireRow.Copy Destination:=wsh.Range("A" & rng.Row)
Case Else
' Do nothing
End Select
Next rng
Application.CutCopyMode = False
End If
End Sub


Thank you so much in advance

Richard

 

[Fluff]

Hi & welcome to MrExcel.
Try

rng.EntireRow.Copy Destination:=wsh.Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[Lavalips]

rng.EntireRow.Copy Destination:=wsh.Range("A" & Rows.Count).End(xlUp).Offset(1)

Hi thank you so much for your response, unfortunately that didn't work, there are still gaps between pasted rows. damned annoying!

 

[Fluff]

There shouldn't be, that change should put the data on the first blank row using col A to find the last used row.
Do you get any errors?

 

[Lavalips]

There shouldn't be, that change should put the data on the first blank row using col A to find the last used row.
Do you get any errors?

none, ill take a screen shot and redact as appropriate

 

[Lavalips]

 

[Fluff]

Do you have values in col A on every row?
Also do you have any merged cells?

 

[Lavalips]

Do you have values in col A on every row?
Also do you have any merged cells?

column A only contains site names where appropriate...... Values start in columns B through to L

 

[Lavalips]

column A only contains site names where appropriate...... Values start in columns B through to L

no merged cells

 

[Fluff]

Ok, try

rng.EntireRow.Copy Destination:=wsh.Range("H" & Rows.Count).End(xlUp).Offset(1, -7)