convert text numbers to real numbers, partial Cell deleting, deleting rows between se

[buhay]

Does anyone know a macro to convert text numbers to real numbers for example in cell A2: CH.000'266'689'8 to CH0002666898

and how to partial delete the text in cell B2 and convert the text numbers to real numbers: Current: SHS -49'553.647 to 49553647

and delete the rows between the CH000... security ID(there are always 8 rows between the security ID).

Do this for the whole sheet containing a lot of balance positions.(see example file)

Any help is highy appreciated

Col A-----------------Col B

Security------------Positions
CH.000'266'689'8---Current: SHS -49'553.647
xxxx xxxx
xxxx xxxx
xxxx xxxx
xxxx
xxxxxx


CH.000'233'570'3---Current: SHS -46'205.371
xxxx xxxx
xxxx xxxx
xxxx xxxx
xxxx
xxxxxx



CH.000'279'222'9----Current: SHS -325'100.092
xxxx xxxx
xxxx xxxx
xxxx xxxx
xxxx
xxxxxx

[macropod]

Hi buhay,

For what you've described and the minimal data you've provided, three simple Find/replace operations would do:
Find = '
Replace = nothing
Find = .
Replace = nothing
Find = -
Replace = nothing

[buhay]

Thanks, does anyone happen to know a macro that deletes all rows if there's a specific text such as "Current: SHS" within the rows?

[macropod]

Thanks, does anyone happen to know a macro that deletes all rows if there's a specific text such as "Current: SHS" within the rows?

Yes, I have such a macro, but are you looking for "Current: SHS" anywhere in the row, or just in a particular column? If it's a particular column, have you considered using sorting or filtering to isolate the rows concerned so they can be deleted?

[buhay]

it doesn't have to be in a particular column or"Current: SHS". I am looking for something that deletes the row containing a specific text since I want to delete all rows with that condition

[macropod]

OK, here's one such macro. With this macro, you nominate the string to match, the column to test, and the row to start at.

Dim SBar As Boolean
Sub CleanUp()
 
Call MacroEntry
Dim iRow, TopRow, RowSet As Long
Dim ColSelect, oStr As String
ColSelect = InputBox("What Column do you want to test?", "Test Column", "A")
TopRow = InputBox("What Row do you want to start at?", "Start Row", "1")
oStr = InputBox("What String do you want to test?", "Test String")
With ActiveSheet
  RowSet = .Cells.SpecialCells(xlCellTypeLastCell).Row - TopRow + 1
  On Error Resume Next
  For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To TopRow Step -1
    Application.StatusBar = "Processing Row " & iRow - TopRow + 1 & " of " & RowSet
    If InStr(.Cells(ColSelect & iRow).Value, oStr) <> 0 Then
      .Rows(iRow).EntireRow.Delete
    End If
  Next iRow
End With
Call MacroExit
End Sub
 
Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub
 
Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

As you can see, there are lots of ways of approaching the issue.

[buhay]

I am just looking for a macro that loops through the sheet and delete all rows containing a specific text or word(no matter what column they occure) but thanks anyway

[macropod]

In that case, you could change the 'CleanUp' sub to:

Sub CleanUp()
Call MacroEntry
Dim iRow, iCol, LastRow As Long
Dim oStr As String
oStr = InputBox("What String do you want to test?", "Test String")
With ActiveSheet
  LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
  On Error Resume Next
  For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
    Application.StatusBar = "Processing Row " & iRow & " of " & LastRow
    For iCol = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Column
      If InStr(.Cells(iRow, iCol).Value, oStr) > 0 Then
        .Rows(iRow).EntireRow.Delete
        Exit For
      End If
    Next iCol
  Next iRow
End With
Call MacroExit
End Sub

[buhay]

I am using excel 2003 and have changed the code as follows:

[VBA]
Sub CleanUp()
Call MacroEntry
Dim iRow, iCol, LastRow As Long
Dim oStr As String
oStr = "Conditional Borrowing: SHS 0"
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
Application.StatusBar = "Processing Row " & iRow & " of " & LastRow
For iCol = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Column
If InStr(.Cells(iRow, iCol).Value, oStr) > 0 Then
.Rows(iRow).EntireRow.Delete
Exit For
End If
Next iCol
Next iRow
End With
Call MacroExit
End Sub

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub[/VBA]

The code is supposed to delete all rows containing the text "Conditional Borrowing: SHS 0" but doesn't seem to work.

[macropod]

Your modified code works for me - even when "Conditional Borrowing: SHS 0" is part of a longer string in a cell.

Do note that, unless you've got the 'Dim SBar As Boolean' as indicated in my earlier post, you won't get any feedback via the status bar. Indeed, if youve got the 'Option Explicit' statement without the 'Dim SBar As Boolean' statement, the code won't even run.

[buhay]

Your modified code works for me - even when "Conditional Borrowing: SHS 0" is part of a longer string in a cell.

Do note that, unless you've got the 'Dim SBar As Boolean' as indicated in my earlier post, you won't get any feedback via the status bar. Indeed, if youve got the 'Option Explicit' statement without the 'Dim SBar As Boolean' statement, the code won't even run.

It works mate! thank you very much for drawing my attention to the 'Dim SBar As Boolean' statement.

Coud you also help me out with finding a macro that is supposed to select all rows with the fonts colour red (in this case row 1 and row 3) and copy paste it from sheet1 to sheet2.

For example:



--------Column A-----Column B--------Column C-----Column D

Row1---- fonts



Row2



Row3 ---------------------------------fonts



Row4

Thanks in advance mate

[macropod]

Hi buhay,

I suggest you start a new thread for that one.

Please also bear in mind that this is not simply a free coding forum. We're here to help, not just to do the work for you. So you should include with your post whatever code you're using and having trouble with, plus an indication of what specific parts of the code aren't working.

[buhay]

Sure thing, I will do that in the future and try to contribute to the forum despite the fact that I am a newbie when it comes to vba
Thanks again for helping me out