I am very new to VBA so forgive me if I'm doing this completely wrong. Basically, to give an overview of what I'm doing, I have a template that is filled out by a user. There are values spread out over the template that I need to add to a table but I don't want to add blanks or duplicates. I started by condensing them in a continuous range and I was able to figure out how to remove the duplicates, but I can't remove the one blank that is left. Also, I'm not sure the best way to add them to the table on another worksheet. This is what I have so far...
Sub UpdateKey()
Range("P10:P36").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Selection.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
Like I said, this always leaves me a blank right in the middle which I don't know how to get rid of, and I don't know where to go from here to add what's left to my table. Thanks for any help!
Try to use following code:
Sub UpdateKey()
With Range("P10:P36")
.Value = .Value
.RemoveDuplicates Columns:=1, Header:=xlNo
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
On Error GoTo 0
End With
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With