IF Statement and Macros

kannon · #1 01-05-2012, 05:41 PM

Hello All -

I am working on a project by which the information I need comes from a very poor table design. Please see attached.

What I want to do is when there are multiple cells for remarks (Column C) to combine them so they can collectively be linked.

I need to do this with about 1800 entries so you can see why I don't want to do by hand.

Thank you very much for any ideas how to proceed.

Kannon

macropod · #2 01-05-2012, 09:23 PM

Hi Kannon,

Try the following macro:

Code:

Sub Reformat()
Application.ScreenUpdating = False
Dim i As Long, j As Long
With ActiveSheet
  .Cells.UnMerge
  For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
  If .Cells(i, 1).Value <> "" Then
    j = i
  Else
    If Trim(.Cells(j, 3).Value) <> "" Then
      .Cells(j, 3).Value = .Cells(j, 3).Value & Chr(10) & .Cells(i, 3).Value
      .Cells(i, 3).Value = ""
    End If
  End If
  Next
  For i = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
    If .Cells(i, 1).Value = "" Then .Cells(i, 1).EntireRow.Delete
  Next
End With
Application.ScreenUpdating = True
End Sub

Pecoflyer · #3 01-06-2012, 02:51 AM

Just wanted to added that using merged cells is looking for trouble if you need any more analysis done

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
What IF statement required	dr4ke	Excel	8	09-01-2011 07:41 AM
Help with IF Statement	limpbizkit	Excel	4	02-24-2011 09:16 PM
if statement	piper7971	PowerPoint	1	08-19-2010 07:10 AM
Help with IF statement!	CPelkey	Word	1	04-12-2010 09:06 AM
Have you seen this error statement?	nebb	Word	4	12-01-2009 10:05 AM