Skip blank cells - horizontally

December 9, 2010 at 16:18:17
Specs: Windows XP
Hi, I've seen couple previous posting in regards of how to get rid of "blank cells." However, my case may be a bit different.
My data are like this....

ItemA (blank) contentB (blank) (blank) (blank) (blank)
ItemB contentA contentB (blank) (blank) (blank) (blank)
ItemC (blank) (blank) (blank) (blank) (blank) contentF
ItemD (blank) (blank) (blank) contentD contentE contentF

This is a gigantic list, and I cannot use sort function. Is there a way to build a formula and/or other methods to skip the blank cell, and put the cells w/ content right next to each other as....

ItemA contentB
ItemB contentA contentB
ItemC contentF
ItemD contentD contentE contentF

Please shed some light for me.
Thank you.


See More: Skip blank cells - horizontally

Report •


#1
December 9, 2010 at 16:49:47
There is no formula that will do what you want.

It is going to require a macro. Assuming your data starts in A1, this code should do it.

I suggest you try the code in a backup copy of your workbook since macros cannot be undone.

Option Explicit
Sub delBlankCells()
Dim lastRw, lastCol, rw, col As Integer
'Find last row with data in Column A
 lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows
  For rw = 1 To lastRw
'Find last column with data in current row
   lastCol = Cells(rw, Columns.Count).End(xlToLeft).Column - 1
'Check each cell in row, delete blank ones
    For col = lastCol To 2 Step -1
     If Cells(rw, col) = "" Then
      Cells(rw, col).Delete shift:=xlToLeft
     End If
    Next
  Next
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 10, 2010 at 09:03:18
Thank you very much.

I've tried the Macro, and it didnt work.
~"~

Guess I gotta do it the old fashion way: Ctrl+C, Ctrl+V.
Thank you once again~


Report •

#3
December 10, 2010 at 10:31:39
Uh...do you always give up that easily? One try and that's it?

Sure, you could do it the old fashioned way or you could tell me what "it didnt work" means and we can try to fix it.

Keep in mind that I can't see your spreadsheet from where I'm sitting. I took a guess that your spreadsheet looked a certain way and wrote some code that I thought might work for you. Since the code works perfectly fine for me, it probably means that my assumption as to your spreadsheet layout was wrong.

If your spreadsheet looks like this...

       A	      B	     C	     D	    E	   F	   G
1   ItemA		  contentB				
2   ItemB	  contentA contentB				
3   ItemC						contentF
4   ItemD				contentD	contentE	contentF

Then my code will produce this, which matches your example output.

      A	      B	      C	     D	    E	   F	   G
1   ItemA	  contentB					
2   ItemB	  contentA contentB				
3   ItemC	  contentF					
4   ItemD	  contentD contentE contentF			


If your spreadsheet is laid out differently, then the code will need to be changed.

Only you can tell me how your spreadsheet is laid out.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •
Related Solutions


Ask Question