Mind Challaging Shift Cells Problem

[Kavy]

Hello, lets see if anyone can get this, or if anyone knows if excel can do this!

first off I wil type my problem, and then explain a bit of it below if needed. I am using excel vb, and all user input will come from user forms.

I would like to know is there anyway when you delted a set of cells to make the rest of the cells shift according to a pattern. I would like them to shift left, unless they are in col 2, in which case I would like there new location to be their row - 1 and col 255. (Kinda like if your using word and you backspace on a new line, it goes to the end of the line above).

Now just a little background info:
I have an worksheet, I am using collumns 2 through 255, col 1 is resvered. I am also using rows 2-64000, row 1 is for headers.

My worksheet is for "Past Due work" and is sort of a DataBase. Collumns 2 through 255 are split into three headings that repeat - WS,Row,Col.

so Row 2 looks like - Revsered,WS,Row,Col,WS,Row,Col,WS,Row,Col ... etc.

The Actuall words, WS,Row,Col go into row 1 and the values go into row 2 and beyond.

This values mark the location in another excel database of uncompleted work. If the work is marked as compelted, the values from this worksheet will dissapper. This causes a prolbem because then I will have blanks in my worksheet,

I would like to know is there anyway when you delted a set of cells to make the rest of the cells shift according to a pattern. I would like them to shift left, unless they are in col 2, in which case I would like there new location to be their row - 1 and col 255. (Kinda like if your using word and you backspace on a new line, it goes to the end of the line above).

Thanks ! good luck!

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

Sub hhh()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
For b = 2 To 255
If Cells(a, b) = "" Then
Cells(a, b).Select
Selection.Delete Shift:=x1ToLeft
End If
Next b
Next a
End Sub

Run the macro. If a cell is blank it should shift the rest of the cells to left. Try it on a sample.
Ravi

 

[Kavy]

thanks! giving it a shot!

 

[Kavy]

It almost works, when I have a blank, it will be 3 collumsn after each other, (ws,row,col) and it seems to miss the first one of the first set of blanks, setting the data off by one col.


Thanks for your help by the way

what does
Cells(Rows.Count, 1).End(xlUp).Row
do?

It also didn't work moving something from another row , I replaced your shiftleft with Shifttoleft, i was suppose to do that i think.
thanks again!

 

[Kavy]

i included a b= b-1 in the if statment, that got rid of making it miss cells, but it created a infite loop!

 

[ravishankar]

Hi
Cells(Rows.Count, 1).End(xlUp).Row counts the last filled row of col 1 (A) It will be around 64,000 in your case. try this modification.

Sub hhh() 
x = Cells(Rows.Count, 1).End(xlUp).Row 
For a = 2 To x 
For b = 2 To 255 step 3
If Cells(a, b) = "" and cells(a,b+1) = "" and cells(a,b+2) = "" Then 
Cells(a, b).Select 
Selection.Delete Shift:=x1ToLeft 
End If 
Next b 
Next a 
End Sub

Ravi

 

[Kavy]

Thanks, as always!