Mind Challaging Shift Cells Problem

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt F11)

Code:
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

Well-known Member
Joined
Jun 25, 2007
Messages
607
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

Well-known Member
Joined
Jun 25, 2007
Messages
607
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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.
Code:
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,272
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top