Remove Row and move cells

[used2reg]

Hello

I have a file dump from my business system and i would like some help on how i cam remove a row; and shift cell contents up one row if needed

i added a sample file here (top what i have; and bottom what i would like to see)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Desc[/TD]
[TD]Loc[/TD]
[TD]Unit[/TD]
[TD]Quant[/TD]
[TD]Cost[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Anchor[/TD]
[TD]Shackle[/TD]
[TD]Primary Location[/TD]
[TD]EA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Antifreeze[/TD]
[TD]Antifreeze[/TD]
[TD]Primary Location[/TD]
[TD]EA
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OilRoom[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]10
[/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0011[/TD]
[TD]Battery[/TD]
[TD]Primary Location[/TD]
[TD]Ea[/TD]
[TD]2[/TD]
[TD]25[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


Ideal report ....

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Desc[/TD]
[TD]Loc[/TD]
[TD]Unit[/TD]
[TD]Quant[/TD]
[TD]Cost[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Anchor[/TD]
[TD]Shackle[/TD]
[TD]Primary Location[/TD]
[TD]EA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Antifreeze[/TD]
[TD]Antifreeze[/TD]
[TD]OilRoom[/TD]
[TD]EA[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0011[/TD]
[TD]Battery[/TD]
[TD]Primary Location[/TD]
[TD]Ea[/TD]
[TD]2[/TD]
[TD]25[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


Item # - In Column A
Description - B
Location - C
Unit - D
Quantity - E
Cost - F
Value - G

Any suggestions?

Thanks

 

[The_Macrotect]

Hi used2reg,

Are you trying to delete rows that have no value in column A?

If that's that case, try this code:

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Is this what you're looking for?

Matt

 

[used2reg]

Matt

What I am actually looking for would be to delete the rows where location is blank and Quantity has a numeric value (either negative, zero, or positive #), and

move the inventory count row up one if we have a PrimaryLocation & a defined location such as Antifreeze above...

 

[The_Macrotect]

I think I understand. Is that what you'd like to do?

This code will:
*Find a row where the location is empty and the quantity is not empty and delete that row
*Fill in Location, Quantity, Cost & Value for that Item using the info from the row directly below it
*Delete the row that was copied from


Sub delete_and_move_up()
Dim count As Long, countC As Long, countE As Long, row As Integer
row = 1

startcheck:
row = row + 1
count = Application.CountA(Range("A" & row & ":G" & row))
countC = Application.CountA(Range("C" & row))
countE = Application.CountA(Range("E" & row))
If count = 0 Then 'If it reached the end of the worksheet (assuming there are no empty rows between data) then:
Range("A1").Select
GoTo QuitScript

ElseIf countC = 0 And countE <> 0 Then 'If location is empty AND quantity is not empty then:
Rows(row).Delete
Range("C" & row - 1).Copy
Range("C" & row - 2).PasteSpecial
Range("E" & row - 1 & ":G" & row - 1).Copy
Range("E" & row - 2).PasteSpecial
Rows(row - 1).Delete
GoTo startcheck

Else: 'If neither of the two conditions are true then
GoTo startcheck
End If


QuitScript:
End Sub

 

[used2reg]

AWESOME !!!! Thanks you so much -