VB excel macro

[jay_mee]

Hi!

I am having problems in VB/Excel, on how to deal with multiple lines in a cell. Some cells contains more than one line within a cell and I need to either delete the other line or concatenate all characters to a single stream of characters using VB Code

Also, I need to do the following:

1. Replace all the character "Ñ" to "N"
2. Delete all hidden columns and rows
3. Number format. Amount fields should not contain any commas

Please help me!!!

I need to consider these points before i convert my excel file into .CSV format


Thanks

[Cole Patzman]

as far as number 1, the following would take care of the "ñ" , this code takes into consideration that an asterisk is in column a after the last record :

Dim char As String
Dim find As String
Dim found As Integer
Dim endit As String
Dim row As Integer
row = 1
endit = "*"
find = "n"
char = "ñ"
Range("A" & row).Select
While ActiveCell.FormulaR1C1 <> "*"
found = InStr(ActiveCell.FormulaR1C1, char)
While found > 0
ActiveCell.FormulaR1C1 = Mid(ActiveCell.FormulaR1C1, 1, (found - 1)) & "n" & Mid(ActiveCell.FormulaR1C1, (found + 1))
found = InStr(ActiveCell.FormulaR1C1, char)
Wend
row = row + 1
Range("A" & row).Select
Wend
End Sub


On problem 3, Highlight the column that contains the dollar amounts and click on format cells. On the next screen select the number tab and make the field numeric, make sure the thousands separator check box isn't checked. If that doesn't work, go back to the same place, check the thousands separator, then deselect. That should format the specific column correctly.

As far as the hidden columns and rows, I will have to get back to you on that.

[Cole Patzman]

This will delete the rows that are hidden. Again, an asterisk has to be in column A in the record after the last record.

Dim row As Integer
row = 1
Range("a" & row).Select
While ActiveCell.FormulaR1C1 <> "*"
Rows(row & ":" & row).Select
If Selection.EntireRow.Hidden = True Then Selection.Delete Shift:=xlUp
row = row + 1
Wend

[Cole Patzman]

Last but not least, this code will review columns A through IV. Any column that is hidden, will be deleted.

Dim col As Integer
col = 1
While col < 206
Columns(col).Select
If Selection.EntireColumn.Hidden = True Then
Selection.Delete Shift:=xlToLeft
endif
col = col + 1
Wend

[Cole Patzman]

Now that your secondary questions are answered, could you send me a sample of the spreadsheet concerning your initial problem?

[jay_mee]

Hi and thanks for your replies...

This is in regard with Number format that I mentioned in my first thread. I need to format the cells using VB and not in excel itself. Can someone help me how to format it in manner that all the comma in the number field will be removed? I need to do it in VB and not in excel since the excel files are already existing and what i want is just to run| call this function in which it will trim down or remove all the commas found in my excel sheet.


Thanks.. please reply asap

[Cole Patzman]

Dim xl_n As String
Dim row As Integer

Private Sub Form_Load()
xl_name = "C:\dollars.xls" 'Put path and file name in place of C:\dollars.xls
Set xl = GetObject(xl_name)
xl.Application.Workbooks(1).Sheets("SHEET1").Select 'Replace all occurances of SHEET1 with the name
'of the worksheet you'll be dealing with.
row = 1
Const a = 1
Dim dollar As String
dollar = Format(xl.Application.Workbooks(1).Worksheets("sheet1").Cells(row, a), "#######0.00")
'If you dont want any decimal places, the format template would be "#######"
xl.Application.Workbooks(1).Worksheets("sheet1").Columns("A:A").NumberFormat = "0"
'Replace the "A:A" in the above line to the column that contains the numeric value

End
End Sub


''''''In the above code which is basically down and dirty, you will need to follow instructions contained in the comments.


If there is something else, please send me an email.
Thanks
Cole

[da_silvy]

Originally posted by Cole Patzman
as far as number 1, the following would take care of the "ñ" , this code takes into consideration that an asterisk is in column a after the last record :

Dim char As String
Dim find As String
Dim found As Integer
Dim endit As String
Dim row As Integer
row = 1
endit = "*"
find = "n"
char = "ñ"
Range("A" & row).Select
While ActiveCell.FormulaR1C1 <> "*"
found = InStr(ActiveCell.FormulaR1C1, char)
While found > 0
ActiveCell.FormulaR1C1 = Mid(ActiveCell.FormulaR1C1, 1, (found - 1)) & "n" & Mid(ActiveCell.FormulaR1C1, (found + 1))
found = InStr(ActiveCell.FormulaR1C1, char)
Wend
row = row + 1
Range("A" & row).Select
Wend
End Sub


On problem 3, Highlight the column that contains the dollar amounts and click on format cells. On the next screen select the number tab and make the field numeric, make sure the thousands separator check box isn't checked. If that doesn't work, go back to the same place, check the thousands separator, then deselect. That should format the specific column correctly.

As far as the hidden columns and rows, I will have to get back to you on that.

Why use a cannon to kill a mosquito?

That is overkill

Cells.Replace What:="Ñ", Replacement:="N", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

1 line of code to replace all of yours, it doesn't rely on a * either

HTH

[Cole Patzman]

Why use a cannon to kill a mosquito? I don't know probably like the MOAB on the Iraqi military, because it was there!

[da_silvy]

If you can do it a simpler way, why complicate things?

[TheBionicOrange]

Originally posted by da_silvy
Why use a cannon to kill a mosquito?

That is overkill

1 line of code to replace all of yours, it doesn't rely on a * either

HTH

I think a more diplomatic approach might have been ... "Hey that works but why not try this instead. Its less code and more manageable"

[opus]

Hi, there is a new FORM "VBA", please use this one for questions like this!

[da_silvy]

Hi

This was posted before the new forum.

I'm not really a diplomat bionic but it would've been nicer