copying text that exceeds the width of the page to the line below

[jip]

Hi evryone, I am looking for a peace of code that will copying the text that exceeds the width of the page to the line below. I will briefly explain where im at: I have some text that I copy from a site, I paste special this in A1, now I have a list of text, all in the 1st colum (A1-aprox. A250-280) I need to fit this on 2 pages(70%). I already made a macro that will delete all the lines I do not want and all the double blanks. But I still have to copy some lines that exceed the width of the page to the line below. I would like my macro to do this aswell. I am using courier new, so the amount of characters / spaces is always the same. So if 100characters is 1 page, i want to cut evrything after the 100st to the line below.
Is this possible and if so, can someone make it for me / help me start it up.
My macro now is this:

Sub Delete()
Dim count As Integer
count = 3
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate

While ActiveCell.Row > 1
If ActiveCell.Value = "--------------------------------------------------------------------------------" Then
ActiveCell.EntireRow.Delete
End If
If ActiveCell.Value = "Click on mutated (underlined) nucleotide to see the original one: " Then
ActiveCell.EntireRow.Delete
End If
If ActiveCell.Value = "Click on mutated (underlined) amino acid to see the original one: " Then
ActiveCell.EntireRow.Delete
End If
If ActiveCell.Value = "Be aware that some allele reference sequences may be incomplete or from cDNAs. In those cases, IMGT/JunctionAnalysis uses automatically the allele *01 for the analysis of the JUNCTION." Then
ActiveCell.EntireRow.Delete
End If

If ActiveCell.Value = "" And ActiveCell.Offset(-1, 0).Value = "" Then
ActiveCell.EntireRow.Delete
End If
If ActiveCell.Value = "2. Alignment for D-GENE and allele identification" Then
ActiveCell.Offset(-1, 0).Activate
While count = 3
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Activate

If ActiveCell.Value = "Alignment with FR-IMGT and CDR-IMGT delimitations" Then
ActiveCell.EntireRow.Delete
count = 2
End If
Wend

End If

ActiveCell.Offset(-1, 0).Activate
Wend

End Sub

Im a VBA newb so this is probably not the best way to do it, but it works great for me. So I start on top, go to the last cell and from there it goes up 1 cell at a time, and there are some if's of lines I dont want. So for text copying lines im thinking: if total characters of line > lets say 100 then cut evrything after 100 to line below. Or maybe you can use colums? evrything > column K to line below. I dont know, and i also dont know the commands, so please help me out!

Thanks in advance!

[patel]

attach a sample file for testing

[jip]

Thanks for the reaction! I cant give you that until monday, when I'm at work again, but can't you test it by simply taking an empty sheet and put aaaaaaaaaaaaaa etc in A1? Basic is the same I think? I can figure out how to adjust it to my file!

[snb]

keep it simple:


sub M_snb()
for j=1 to 4
columns(1).replace choose(j,"--*","Click *","Be *","Alignment *""),""
next
columns(1).specialcells(4).entirerow.delete
columns(1).WrapText = True
end Sub

[jip]

keep it simple:


sub M_snb()
for j=1 to 4
columns(1).replace choose(j,"--*","Click *","Be *","Alignment *""),""
next
columns(1).specialcells(4).entirerow.delete
columns(1).WrapText = True
end Sub


Can you briefly explain what the lines do? So i know how to adjust it to my preferences. Thanks a lot!

[snb]

They do what you asked for.
You can test it in your file and appraise the results.

[patel]

very good and simple solution :thumb:thumb

[jip]

They do what you asked for.
You can test it in your file and appraise the results.
thanks a lot, it does what i asked for indeed. But the only problem now is that it puts anything that exceeds the width of 1 cell, below. How do I change it to 5 cells or 10 cells of what ever? I'm sorry I don't understand enough to figure it out myself.. I tried, but cant figure it out

[snb]

Why do you want to split the data into several cells ?

[jip]

Why do you want to split the data into several cells ?
I do not need that, i dont care if it is in the below Or below in the Same cell. But now everything >A gets put to the line below, but How do i change it to everything >lets say column K? Thanks for the help btw, appreciate it à lot!

[snb]

I have no clue what you are talking about. Please post a sample file and illustrate what you mean.

[jip]

I have no clue what you are talking about. Please post a sample file and illustrate what you mean.
Alright. I uploaded a sample file here:
http://speedy.sh/S9Kqj/Sample-file.xls
So what my macro does is delete all the double blanks, delete unnecessary rows like: 11, 93, 183, 191 etc. I want to print this file so it has to fit on 2 pages (portrait view, not landscape). So i need to copy the text that exceeds that width of the page like in lines 176, 186 and 188 to the line below. If necessary insert a row. So what i want the macro to do in lets say row 188 is copy "4 2,183.34 4.42" or "14 2,183.34 4.42" that doesnt really matter, to the row below, so that will be row 189. Or keep it in row 188, and make this row bigger so it will fit, thats also fine. When you open page setup and close it again you can see the margings, so i want evrything after column M, to go down.
The number of the row is not the same in every file, so this time its row 188, next time it can be row 192 or 180 or what ever.

Hope it is clear now! Thanks!

[snb]

Did you read the PM I sent you ?

[jip]

Did you read the PM I sent you ?
Yes I did, but couldnt reply because I need at least 10 posts.. I thought you could help me so I didnt post it on worksheet.nl yet. Can you help me or should I post it there aswell?

[snb]

Since you only use column A:
add this line in the macro:

Columns(1).ColumnWidth = 240

You have to adjust this width to the printersettings, margins, etc.
But you can do this manually as well.

[jip]

Since you only use column A:
add this line in the macro:

Columns(1).ColumnWidth = 240

You have to adjust this width to the printersettings, margins, etc.
But you can do this manually as well.
Thanks a lot for the help, I am now just using:
Columns(1).ColumnWidth = 130
columns(1).WrapText = True

That was all I needed, thanks a lot, saves a lot of time!

[snb]

Please do not quote....