VBA to delete range of cells and copy paste

[padadof2]

I have an excel sheet that we use to estimate quantities, etc. We will sometimes delete rows since we are not using that task code and leave it blank. I have been copy/pasting these and then deleting blanks and then formatting each time I need to submit an estimate. I have been trying to copy cells A1:K49 to new sheet, then delete the empty cells using VBA and some codes to clean up the files. I had originally has all the information auto populate into a new worksheet, but I couldn't get the code to work correctly. I'm pulling hair out (that I don't have) trying to get this to work. I was able to get a code to find the correct rows and delete them, but it's always the WHOLE row, not just the section I need to delete.
This was the code I was trying to use that I found online if it helps.


Sub RemoveBlankRows()


Dim rng As Range
Dim what As String
what = "FA1-0"


'Store blank cells inside a variable
On Error GoTo NoBlanksFound
Set rng = Range("AR5:BB44").Find(what)
On Error GoTo 0


'Delete entire row of blank cells found
rng.Delete

Exit Sub


'ERROR HANLDER
NoBlanksFound:
MsgBox "No Blank cells were found"


End Sub




This code was used for the sheet when it auto Populates, but as I have been struggling to get this to work, I'd think it would be just as easy to do it all in VBA (I'm a noob, and could be wrong).


https://www.dropbox.com/s/c6isgx64uzvtzc0/vba example.xlsx?dl=0 is the sample excel file if needed

Thanks in advance again.

 

[Rhodie72]

I have an excel sheet that we use to estimate quantities, etc. We will sometimes delete rows since we are not using that task code and leave it blank. I have been copy/pasting these and then deleting blanks and then formatting each time I need to submit an estimate. I have been trying to copy cells A1:K49 to new sheet, then delete the empty cells using VBA and some codes to clean up the files. I had originally has all the information auto populate into a new worksheet, but I couldn't get the code to work correctly. I'm pulling hair out (that I don't have) trying to get this to work. I was able to get a code to find the correct rows and delete them, but it's always the WHOLE row, not just the section I need to delete.
This was the code I was trying to use that I found online if it helps.

Sub RemoveBlankRows()


Dim rng As Range
Dim what As String
what = "FA1-0"


'Store blank cells inside a variable
  On Error GoTo NoBlanksFound
    Set rng = Range("AR5:BB44").Find(what)
  On Error GoTo 0


'Delete entire row of blank cells found
  rng.Delete
  
Exit Sub


'ERROR HANLDER
NoBlanksFound:
  MsgBox "No Blank cells were found"


End Sub

This code was used for the sheet when it auto Populates, but as I have been struggling to get this to work, I'd think it would be just as easy to do it all in VBA (I'm a noob, and could be wrong).


https://www.dropbox.com/s/c6isgx64uzvtzc0/vba example.xlsx?dl=0 is the sample excel file if needed

Thanks in advance again.

Maybe this is what you're after?

Sub RemoveBlankRows()
Dim Rng As Range, _
    what As String, Qry As String

    Qry = "FA1-0"

' Store blank cells inside a variable
    On Error GoTo ERROR_HANLDER
    Set Rng = Range("AR5:BB44").Find(Qry)

    On Error GoTo 0
' Delete entire row of blank cells found
    Selection.EntireRow.Delete

Exit Sub
ERROR_HANLDER:
    MsgBox "No Blank cells were found"
End Sub