Hello, i have data in sheet1 from range a1: f91 when i fill data and transfer to sheet2 it also transfer the empty rows here i wanna delete the rows what contains cells from a75: a88 just in specific one condition cells a =" " then delete the rows and without effect the total value from a89:a91
the used my code is:
Sub TransferData()
' 20 Jun 2019
Dim Target As Range
Dim LastRow As Long
Dim R As Long
LastRow = sheet1.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
Set Target = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp)
For R = 2 To LastRow
sheet1.Range(sheet1.Cells(R, 1), sheet1.Cells(R, 6)).Copy _
Destination:=Target.Offset(R - 1)
With Target.Offset(R - 1, 4)
If .HasFormula Then .Value = sheet1.Cells(R, 5).Value
End With
Next R
Target.Offset(LastRow, 5).Value = sheet2.Range("E91").Value
End Sub
As for not moving the end cells, you may need to record that value into a variable, then place that variable back down into the cell you wish to have it, after all of the deletes. As a deleted cell needs to go away, or you will just be deleting the contents, and therefor not accomplishing anything!
i do this code and give me the error run-time 1004 application- defined or object defined error
Sub save()
Dim ss As Range
Set ss = Worksheets("sheet2").Range("a75:a88")
ThisWorkbook.Worksheets("sheet2").Activate
LastRow = sheet2.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
Range("A2", "A" & LastRow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub
If you press, DEBUG, it will highlight the line it is stopped on.....and the error says what is wrong.......
So do you have blank items items in the Cells of the range that is highlighted? If not, it will throw an error!
If you are definingg the range with hard code, then you do not need to find the last row, as you are already defining it!
This will still throw 1004 error, but it is what you were attempting (I think!).
Sub save() ThisWorkbook.Worksheets("sheet2").Activate Range("a75:a88").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Shift:=xlUp End Sub
You are defining a sheet, but not doing anything with it! You never reference SS again! So I am confused as to why it is even there.....
i know how i can find the error in line but the window what shows is not like your picture thats why it doesn't allow me where the exact the errors and still continue the error and i change the code to help me where is the error and correct me because i'm newer in vba
OK. Are there values in the cells that are highlighted? I can duplicate the error when there are no duplicates within the range that happens to be selected. It is just the return of NO DUPLICATES. Do this:
1. clear the sheet that you are copying to.
2. Make the sheet that you want to transfer have focus
3. Press RUN.
4. post the feedback (as I only get this error, after I have ALREADY REMOVED the DUPLICATES)
