Shift up values to fill in blank cells

[bujubenji]

Evening all,

I wonder if you can provide some advice. I have a sheet in place which records when goods are sent with the click of a button "btnR44"
This will copy cells across to another range. Please see code here for clarity :

"Sub btnR44_click()

With ActiveSheet

Range("B44:E44").Copy
Range("B44:E44").Interior.Color = RGB(136, 212, 138)
.Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

.Shapes("btnR44").Visible = False
.Shapes("R44clk").Visible = True

Application.Speech.Speak "sent to Q.A. ", speakasync:=True

End With
End Sub"

I am attempting to code the removal of info that was sent over in the event of human error or similar. i.e. B44:E44 holds info on a particular good, I click a button on row 44 which copies the info over to H44:K44 and so on down to row 58.

If for example, someone mistakenly pressed that button, I want a secondary click to remove the data from H44:K44 and shift any subsequent data up to fill in the blank row.


Sub btnR44_unclick()

With ActiveSheet

.Shapes("btnR44").Visible = True
.Shapes("R44clk").Visible = False
Range("B44:E44").Interior.Color = xlNone

Dim rng As Range
Set rng = Range("h44:K58").Find(what:=Range("B44:E44").Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
rng.ClearContents
rng.Offset(0, 1).ClearContents
rng.Offset(0, 2).ClearContents
rng.Offset(0, 3).ClearContents

'here I want to make it so that the blank row which is now present, will be filled by the subsequent row of data.
'or in other words, I want to shift up all values to fill in any blank row in the range H44:K58
'I found the below on another forum, but it results in a Run-time error '1004' Application-defined or object-defined error'

With Range("H44:K58")
For i = .Rows.Count To 1 Step -1
If IsEmpty(.Cells(i, 1)) Then .Rows(i).Clear Shift:=xlUp 'This snippet is highlighted on debug

Next
End With
End With
End Sub

Any help would be fantastic!

Thanks.

 

[johnnyL]

...
If for example, someone mistakenly pressed that button, I want a secondary click to remove the data from H44:K44 and shift any subsequent data up to fill in the blank row.
...

How about:

    Range("H44:K44").Delete

 

[bujubenji]

Solution:

Dim rng As Range
Set rng = Range("H44:K58").Find(what:=Range("B44:E44").Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
rng.ClearContents
rng.Offset(0, 1).ClearContents
rng.Offset(0, 2).ClearContents
rng.Offset(0, 3).ClearContents
Range("H44:K58").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp