VBA: Delete empty rows based on value of cells

Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
-
Hello,
my code works transfer my invoice from sheet1 to sheet 2 but my problem i would adjusted my code to become delete rows from a19: e32 if cells a19:a32=" " when press my macro becarful delete theses rows in sheet2 not sheet1 taking into consideration maybe occurs error in total the total =e33 and connected with last row filled data for instance the last row is a20:e20 so e33= e20
this is my code
Sub TransferData()

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, 5)).Copy _
Destination:=Target.Offset(R - 2)
With Target.Offset(R - 2, 4)
If .HasFormula Then .Value = sheet1.Cells(R, 5).Value
End With
Next R

On Error Resume Next ' error if the is a REF error in the sum range
Target.Offset(R - 3, 5).Value = WorksheetFunction.Sum(sheet2.Range("E33:E35"))
End Sub
See more 

4 replies

Posts
2538
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 17, 2019
368
0
Thank you
Hi Abdel,

Give the following code lines a try:
Dim cBlanks as Integer
cBlanks = WorksheetFunction.CountBlank(Range("A19:A32"))
If cBlanks = 14 then Range("A19:E32").ClearContents


Best regards,
Trowa
Respond to TrowaD
Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
0
Thank you
it' doesn't work for more explenation
sheet 1 :


sheet2:





but what i would the result in sheet2:





note: as i said you earlier the total is connected with last row filled data
Respond to abdelfatah_0230
Posts
2538
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 17, 2019
368
0
Thank you
Hi Abdel,

Quote
delete rows from a19: e32 if cells a19:a32=" "
Unquote

This is what the provided code lines were for.


Now I understand you want to delete single rows (row 19 to 31) in where there is no value in column A.
For that try the following:
Dim x As Integer
For x = 31 To 19 Step -1
    If Sheet2.Range("A" & x).Value = vbNullString Then Sheet2.Range("A" & x).EntireRow.Delete
Next x


Row 32 is excluded, since that is your total row.

Hopefully this is more to your liking.

Best regards,
Trowa
Respond to TrowaD
Posts
16
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 17, 2019
0
Thank you
there is no change the same thing this is what shows in sheet2


and this is my file
https://ufile.io/3jb6t9fe
Respond to abdelfatah_0230