PDA

View Full Version : [SOLVED] Copy-Paste cell text content + delete rows



Ziad
03-29-2017, 02:47 AM
Good morning everyone,

I am a complete newbie in Excel VBA programming, and I am not sure I'll be able to write code myself...

To begin with, here is what my tables look like, more or less. I have loads of data, with this set up. Data is spread on several sheets.




A
B
C


1
AAAAAAA
10 Oct 206
bla bla bla bla


2


bla bla bla bla


3
AAAAAAA
10 Oct 206
bla bla bla bla


4


bla bla bla bla


5
AAAAAAA
10 Oct 206
bla bla bla bla


6


bla bla bla bla


7


bla bla bla bla


8
AAAAAAA
10 Oct 206
bla bla bla bla


9


bla bla bla bla



What I want to do is clean up all this mess:

copy text from C2 to end of C1, separated with a blank space. These are sentences, and I want the full sentence in one cell
delete row 2 once C2 is empty
copy all data to a new sheet, so that I hav everything on a single sheet.


Considering that I have cases where I want to copy C6 to end of C5, then C7 to end of C5, and delete both rows 6 & 7.

I have found a looong workaround through a formula : D1=IF(B1<>"";C1&" "&C2;"")
It works, but that would be too long to use with the entire documents.

I have looked up some VBA coding, but I have to say that whatever I try, it remains foreign language to me, and I can't get my head round it.

Any help is welcome, and will receive my eternal gratitude in the form of a huge smile to my screen.

Thanks.

mdmackillop
03-29-2017, 03:49 AM
Here's a generic solution I use on Selections for such which may be of use in minor "messes". No time now for your specific issue.

Sub DoJoinDel()
Dim Rng As Range, cel As Range, txt As String
Dim i As Long
Set Rng = Selection
For i = 1 To Selection.Count
txt = txt & Trim(Rng(i)) & " "
Next
Rng.ClearContents
Rng(1) = Trim(txt)
For i = Selection.Count To 2 Step -1
If Application.CountA(Rng(i).EntireRow) = 0 Then Rng(i).EntireRow.Delete xlUp
Next
ActiveCell.Offset(1).Select
End Sub

Ziad
03-29-2017, 03:54 AM
That's already great ! Thanks mdmackillop
I'll check that, see what I can do with it, and come back to you.
Specific solutions still welcome

mdmackillop
03-29-2017, 06:10 AM
Option Explicit
Sub Test()
Dim r As Range, cel As Range
Dim i As Long, j As Long
Dim txt As String
Dim Sh As Worksheet, ws As Worksheet


Set Sh = Sheets.Add(Sheets(1))
Sh.Name = "Summary"


For j = 2 To Sheets.Count
With Sheets(j)
Set r = .Columns(3).SpecialCells(xlCellTypeConstants)
For i = r.Count To 1 Step -1
If .Cells(i, 2) = "" Then
txt = .Cells(i, 3) & " " & txt
.Cells(i, 3).EntireRow.Delete
Else
txt = .Cells(i, 3) & " " & txt
.Cells(i, 3) = Trim(txt)
txt = ""
End If
Next i
.Cells(1, 1).CurrentRegion.Copy Sh.Cells(Rows.Count, 1).End(xlUp)(2)
End With
Next j
End Sub

Ziad
03-29-2017, 09:36 AM
Wow !

Mate, you just saved me days of work on these spreadsheets !

Thank you so much.

Marking this as solved then.