Expert: Bhaskar - 10/1/2007

Question
QUESTION: Hello Bhaskar,
If I have a column or row of numbers with some blank cells and I wish to tighten it up, i.e. eliminate all the blank cells. Can I do this.
Thanks
Brian

ANSWER: Brian,

Try this code. This assumes that you have the start cell selected when you run the macro. It will pop up a msg asking whether you want to tighten the Row or column...

hope his helps. would appreciate if you would rate the answer.


Option Explicit


Public Sub tighten_cells()

Dim row_or_col As Long
Dim xlR As Excel.Range
Dim xls As Excel.Worksheet
Dim xlr2 As Excel.Range

Dim i As Long, new_i As Long
Dim start_col As Long, start_row As Long, end_col As Long, end_row As Long

row_or_col = MsgBox("Press Yes for Row Tighten, No for Column Tighten, Cancel for Cancelling", vbYesNoCancel)

If row_or_col = vbCancel Then
   Exit Sub
End If


Set xls = Excel.ActiveSheet
Set xlR = Excel.ActiveCell
Set xlr2 = xls.Cells.SpecialCells(xlCellTypeLastCell)

start_row = xlR.Row
start_col = xlR.Column

If (row_or_col = vbYes) Then ' Row Tighten -> move across columns
   new_i = start_col
   For i = start_col To xlr2.Column
       If (xls.Cells(start_row, i) <> "") Then
         If (new_i <> i) Then
         xls.Cells(start_row, new_i) = xls.Cells(start_row, i)
         xls.Cells(start_row, i) = ""
         End If
         new_i = new_i + 1
       End If
   Next i
End If

If (row_or_col = vbNo) Then ' Col Tighten -> move across rows
   new_i = start_row
   For i = start_row To xlr2.Row
       If (xls.Cells(i, start_col) <> "") Then
         If (new_i <> i) Then
         xls.Cells(new_i, start_col) = xls.Cells(i, start_col)
         xls.Cells(i, start_col) = ""
         End If
         new_i = new_i + 1
       End If
   Next i

End If



End Sub




---------- FOLLOW-UP ----------

QUESTION: Hello Bhaskar,
Thanks for your response. Unfortunately I think you're a bit ahead of me. I have never used macros and not quite sure what they are. Could you maybe explain how I run the macro you sent. I have only used excel for very basic formulas etc.
Thanks,
Brian

ANSWER: Brian,

1. Press Alt+F11
2. Click on Microsoft Excel Objects
3. right click and insert module
4. Insert the code
5. Save
6. Close the Visual Basic Editor
7. Go to Excel
8. Click on the cell you want to tighen
9. Click on Tools -> Macro -> Macro
10. Run the macro
11. Select whether you need to tighten the row or column


hope this helps
bhaskar


---------- FOLLOW-UP ----------

QUESTION: Hello Bhaskar,

Not having much luck I'm afraid. When Visual Basic comes up I don't see any "Microsoft Excel Objects"

Brian

Answer
Brian,

When you get to Visual Basic Editor, click Ctrl+R. This should show the "Project Window". Click on "VBProject". Right Click and click on Insert Module.

It might help if you get an Excel book if you need to make use of Macros and other complex stuff.

- Bhaskar