Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-05-2012, 05:41 PM
kannon kannon is offline IF Statement and Macros Windows 7 64bit IF Statement and Macros Office 2007
Novice
IF Statement and Macros
 
Join Date: Jan 2012
Posts: 3
kannon is on a distinguished road
Default How to Merge/Delete Cells Vertically

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
Attached Files
File Type: xlsx Excel Coversion Problem.xlsx (9.4 KB, 13 views)

Last edited by kannon; 01-05-2012 at 08:50 PM. Reason: table mistake
Reply With Quote
  #2  
Old 01-05-2012, 09:23 PM
macropod's Avatar
macropod macropod is offline IF Statement and Macros Windows 7 64bit IF Statement and Macros Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-06-2012, 02:51 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF Statement and Macros Windows XP IF Statement and Macros Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Just wanted to added that using merged cells is looking for trouble if you need any more analysis done
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF Statement and Macros What IF statement required dr4ke Excel 8 09-01-2011 07:41 AM
IF Statement and Macros Help with IF Statement limpbizkit Excel 4 02-24-2011 09:16 PM
IF Statement and Macros if statement piper7971 PowerPoint 1 08-19-2010 07:10 AM
IF Statement and Macros 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft