Howdie all.

I have a macro that looks for the generic contents of a choosable number of
cells.

If it finds contents, it cuts those contents from each of the cells, it then
merges the cells chosen, and pastes the values from those cells into the
merged cell group.
Thus far it works well, but I would like to modify it.

Presently, the issue I'm facing is if there are any blank cells, it inputs a
vbLf, or chr(10) input for those cells.

What I'd like to do is- if a cell is blank, I do not want a character line
return placed there.

The code for this macro is:
-----------------------------------------------------------------
Dim myRng As Range
Dim NewString As String

Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to Meld", Type:=8)
If myRng Is Nothing Then End

If myRng.Cells.count 8 Then
msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
GoTo InputRange
End If

For cell = 1 To myRng.Cells.count

If NewString = "" Then
NewString = myRng.Cells(cell, 1).Value
Else
NewString = NewString & vbLf & myRng.Cells(cell, 1).Value
'this NewString allows a character return to have a new line.
'here is where I think that my modification should occur.
'I don't want rows at the end of my cell contents to be counted if there
' are no contents there.
'Elseif newstring or cell = "" then
'do not include.


End If

myRng.Cells(cell, 1).ClearContents
Next

myRng.Cells(1, 1) = NewString
myRng.MergeCells = True
myRng.WrapText = True
myRng.HorizontalAlignment = xlCenter
myRng.VerticalAlignment = xlCenter
----------------------------------------------------------------

E.g. I have 4 rows.
Rows 1, and 2 have some generic contents as text. Rows 3 and 4 have nothing
in them.
I want to only include rows one and two, but then am going to merge all 4
rows.

Thank you for your helps.
Best.

Hello again Steve

I think this is what you want:

If NewString = "" Then
NewString = MyRng.Cells(cell, 1).Value
ElseIf MyRng.Cells(cell, 1).Value < "" Then
NewString = NewString & vbLf & MyRng.Cells(cell, 1).Value
End If

Best regards,
Per

On 15 Jul., 23:55, SteveDB1
wrote:
Howdie all.

I have a macro that looks for the generic contents of a choosable number of
cells.

If it finds contents, it cuts those contents from each of the cells, it then
merges the cells chosen, and pastes the values from those cells into the
merged cell group.
Thus far it works well, but I would like to modify it.

Presently, the issue I'm facing is if there are any blank cells, it inputs a
vbLf, or chr(10) input for those cells.

What I'd like to do is- if a cell is blank, I do not want a character line
return placed there.

The code for this macro is:
-----------------------------------------------------------------
Dim myRng As Range
Dim NewString As String

Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to Meld", Type:=8)
If myRng Is Nothing Then End

If myRng.Cells.count 8 Then
* * msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
* * GoTo InputRange
End If

For cell = 1 To myRng.Cells.count

* * If NewString = "" Then
* * * * NewString = myRng.Cells(cell, 1).Value
* * Else
* * * * NewString = NewString & vbLf & myRng.Cells(cell, 1).Value
* * * * * * 'this NewString allows a character return to have a new line.
'here is where I think that my modification should occur.
'I don't want rows at the end of my cell contents to be counted if there
' are no contents there. *
'Elseif newstring or cell = "" then
'do not include.

* * End If

* * myRng.Cells(cell, 1).ClearContents
*Next

myRng.Cells(1, 1) = NewString
myRng.MergeCells = True
myRng.WrapText = True
myRng.HorizontalAlignment = xlCenter
myRng.VerticalAlignment = xlCenter
----------------------------------------------------------------

E.g. I have 4 rows.
Rows 1, and 2 have some generic contents as text. Rows 3 and 4 have nothing
in them.
I want to only include rows one and two, but then am going to merge all 4
rows.

Thank you for your helps.
Best.

The code you have posted only works with what the user selects he

Set myRng = Application.InputBox(prompt:="Select cells to Meld", Type:=8)

Then this code:

For cell = 1 To myRng.Cells.count

counts the cells in the selections, not the rows. However, this code:

If NewString = "" Then
NewString = myRng.Cells(cell, 1).Value
Else
NewString = NewString & vbLf & myRng.Cells(cell, 1).Value

is using the variable "cell" as a row counter. This means that if the
selection has a four column by 2 row range, it will contain the allowable 8
cells, but your For ... Next statement will execute against 8 rows, 6 of
which were not in the selection.

My suggestion would be to change the "For cell =" statement to:

For cell = 1 To myRng.Rows.Count

Now. The other problem I see is, that if the user does select more than one
column in the myRange inputbox, only the data in the left column will be
considered. Anything right of that column is ignored. Maybe you want to
reconsider the whole setup.


"SteveDB1" wrote:

Howdie all.

I have a macro that looks for the generic contents of a choosable number of
cells.

If it finds contents, it cuts those contents from each of the cells, it then
merges the cells chosen, and pastes the values from those cells into the
merged cell group.
Thus far it works well, but I would like to modify it.

Presently, the issue I'm facing is if there are any blank cells, it inputs a
vbLf, or chr(10) input for those cells.

What I'd like to do is- if a cell is blank, I do not want a character line
return placed there.

The code for this macro is:
-----------------------------------------------------------------
Dim myRng As Range
Dim NewString As String

Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to Meld", Type:=8)
If myRng Is Nothing Then End

If myRng.Cells.count 8 Then
msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
GoTo InputRange
End If

For cell = 1 To myRng.Cells.count

If NewString = "" Then
NewString = myRng.Cells(cell, 1).Value
Else
NewString = NewString & vbLf & myRng.Cells(cell, 1).Value
'this NewString allows a character return to have a new line.
'here is where I think that my modification should occur.
'I don't want rows at the end of my cell contents to be counted if there
' are no contents there.
'Elseif newstring or cell = "" then
'do not include.


End If

myRng.Cells(cell, 1).ClearContents
Next

myRng.Cells(1, 1) = NewString
myRng.MergeCells = True
myRng.WrapText = True
myRng.HorizontalAlignment = xlCenter
myRng.VerticalAlignment = xlCenter
----------------------------------------------------------------

E.g. I have 4 rows.
Rows 1, and 2 have some generic contents as text. Rows 3 and 4 have nothing
in them.
I want to only include rows one and two, but then am going to merge all 4
rows.

Thank you for your helps.
Best.

JLG,
Thanks for the response.
This is a single column operation.
Anything I do with this form of it will always be a single column operation.
If for some reason I ever find the need to do a two column show, I'll modify
it then.
At this point, I'm interested in only modifying the part that'd place a vbLf
in empty rows.


"JLGWhiz" wrote:

The code you have posted only works with what the user selects he

Set myRng = Application.InputBox(prompt:="Select cells to Meld", Type:=8)

Then this code:

For cell = 1 To myRng.Cells.count

counts the cells in the selections, not the rows. However, this code:

If NewString = "" Then
NewString = myRng.Cells(cell, 1).Value
Else
NewString = NewString & vbLf & myRng.Cells(cell, 1).Value

is using the variable "cell" as a row counter. This means that if the
selection has a four column by 2 row range, it will contain the allowable 8
cells, but your For ... Next statement will execute against 8 rows, 6 of
which were not in the selection.

My suggestion would be to change the "For cell =" statement to:

For cell = 1 To myRng.Rows.Count

Now. The other problem I see is, that if the user does select more than one
column in the myRange inputbox, only the data in the left column will be
considered. Anything right of that column is ignored. Maybe you want to
reconsider the whole setup.


"SteveDB1" wrote:

Howdie all.

I have a macro that looks for the generic contents of a choosable number of
cells.

If it finds contents, it cuts those contents from each of the cells, it then
merges the cells chosen, and pastes the values from those cells into the
merged cell group.
Thus far it works well, but I would like to modify it.

Presently, the issue I'm facing is if there are any blank cells, it inputs a
vbLf, or chr(10) input for those cells.

What I'd like to do is- if a cell is blank, I do not want a character line
return placed there.

The code for this macro is:
-----------------------------------------------------------------
Dim myRng As Range
Dim NewString As String

Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to Meld", Type:=8)
If myRng Is Nothing Then End

If myRng.Cells.count 8 Then
msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
GoTo InputRange
End If

For cell = 1 To myRng.Cells.count

If NewString = "" Then
NewString = myRng.Cells(cell, 1).Value
Else
NewString = NewString & vbLf & myRng.Cells(cell, 1).Value
'this NewString allows a character return to have a new line.
'here is where I think that my modification should occur.
'I don't want rows at the end of my cell contents to be counted if there
' are no contents there.
'Elseif newstring or cell = "" then
'do not include.


End If

myRng.Cells(cell, 1).ClearContents
Next

myRng.Cells(1, 1) = NewString
myRng.MergeCells = True
myRng.WrapText = True
myRng.HorizontalAlignment = xlCenter
myRng.VerticalAlignment = xlCenter
----------------------------------------------------------------

E.g. I have 4 rows.
Rows 1, and 2 have some generic contents as text. Rows 3 and 4 have nothing
in them.
I want to only include rows one and two, but then am going to merge all 4
rows.

Thank you for your helps.
Best.

Bingo!
Thanks Per.
That did it.
Best.


"Per Jessen" wrote:

Hello again Steve

I think this is what you want:

If NewString = "" Then
NewString = MyRng.Cells(cell, 1).Value
ElseIf MyRng.Cells(cell, 1).Value < "" Then
NewString = NewString & vbLf & MyRng.Cells(cell, 1).Value
End If

Best regards,
Per