Excel help required.......

[indigomatt]

Morning...before I throw my computer out of the window :mad: ...someone here may know how to help me, that is apart from a bottle of gin :)

I’ve a number of excel sheet with over 500+ lines and using the ‘find all’ function I can locate incidents of a particular word, but instead of then having to ‘cut and paste’ each line :sleepy: I just want to be able to delete those lines without occurrences...

What fun I'm having...! :D

Ta, Matthew.

[Big Col]

Go up to the edit on the top taskbar mate. You can use replace instead of find. :)

[Big Col]

<blinks> Hang on. I've misread that! Ok are the words all in the same column or is the word spread accross lots of columns/rows?

[euan]

You could create a pivot table with a rule that says to select all which match your criteria, although that's as far as my excel knowledge will allow me to comment!

[Gismo]

I don't understand the question :p

[indigomatt]

Spreadsheet is 50+ columns wide, a mixture of text and numbers...

I want to append a column at the end that gives true false answer if a word say “text” appears in any cell within that row...

Somebody pass me the gin :D

[Burple]

Hold on... I'll have a fiddle, I teach a basics Excel course to staff at the Uni...

(hope this isn't for Uni work young man ;):p)

[Gismo]

quote:Originally posted by low_n_loud1
Hold on... I'll have a fiddle
<sniggers> :p

[Wul]

He's been away at the fiddle for a while now!! :p

[indigomatt]

quote:Originally posted by Wul

He's been away at the fiddle for a while now!! :p


Must be getting a bit carried away, hope he's not making a mess :D

[AndyP & Lenore]

Sorry to hijack the thread, but does anyone still use Lotus 123 instead of Excel?

We have about 50 forms and sheets all designed and running in 123, and to convert/redesign entirely in excel is a mindboggling task which I don't cherish.:(:(

[Burple]

Righty... here's one way to do it that involves a little bit of work, but means you don't have to fark around with anything complicated (which is what I assumed you'd like ;):D)

use the function =OR(EXACT("text", A5:E5)) in the cell at the end of the first row you want to apply the function to.

where "text" is the word you want to find, and A5:E5 is the range of the row you want to apply the formula to (change these to match your spreadsheet). now the trickier bit ;). once you've entered the formula to suit your spreadsheet, click on that cell, then press the f2 on your keyboard, followed by the CTRL + SHIFT + ENTER keys (at the same time). This should give you a true or false result for the first row. now, see at the bottom right corner of that cell with 'true' or 'false' in it? There's a wee black square in that corner called the 'Fill Handle'. Click on it (and hold the click - don't release) and drag the pointer down to the cell at the furthest bottom right (obviously directly below the one you entered the formula in). When it's highlighted to your satisfaction, release the click.. That should ensure that you have a true or false cell at the end of each row, now you can filter on that column. :D

any use?

[Burple]

quote:Originally posted by Bonnie Scotland


quote:Originally posted by low_n_loud1
Hold on... I'll have a fiddle
<sniggers> :p


<smack>

[Burple]

quote:Originally posted by Wul

He's been away at the fiddle for a while now!! :p


<smack>

[Burple]

quote:Originally posted by indigomatt


quote:Originally posted by Wul

He's been away at the fiddle for a while now!! :p


Must be getting a bit carried away, hope he's not making a mess :D


<Smack!>


...WhyIOughta.... ;):p:D

[Wul]

quote:Originally posted by AndyP & Lenore

Sorry to hijack the thread, but does anyone still use Lotus 123 instead of Excel?

We have about 50 forms and sheets all designed and running in 123, and to convert/redesign entirely in excel is a mindboggling task which I don't cherish.:(:(


LOL - Lotus123 - so you are a fan of wysiwyg then!! :clown::I

I trust you've tried simply opening them in excel and then saving them as .xls - excel should open them no probs.

[Burple]

quote:Originally posted by Wul


quote:Originally posted by AndyP & Lenore

Sorry to hijack the thread, but does anyone still use Lotus 123 instead of Excel?

We have about 50 forms and sheets all designed and running in 123, and to convert/redesign entirely in excel is a mindboggling task which I don't cherish.:(:(


LOL - Lotus123 - so you are a fan of wysiwyg then!! :clown::I

I trust you've tried simply opening them in excel and then saving them as .xls - excel should open them no probs.


Can you export stuff from 123 in a delimited text file the same way you can in Excel? If you could split it up into one sheet at a time, you couldexport, then import each sheet in to Excel as a delimited .CSV file.... :p:D;)

[indigomatt]

quote:Originally posted by low_n_loud1

hope this isn't for Uni work young man ;):p




Nope, unfortunately I’m no longer at uni, ma liver needed the rest :D

It all works, I'm so happy :D, well kinda as I've now work to do :dead: pass the gin :p

You’re a genius I worship at your feet :approve:

Many thanks,
M.

[indigomatt]

:dead: I may have spoken to soon *sobs*

It works but does'nt seem to notice the searched for word when it's in a sentence...:question:

Probably just me being pathetic at excel :p

Will have a fiddle about with it...:D

[Burple]

quote:Originally posted by indigomatt

:dead: I may have spoken to soon *sobs*


Oh Oh...



Look! It's Elvis!

<runs for the door> :D:D:p:p

[Burple]

quote:Originally posted by indigomatt

:dead: I may have spoken to soon *sobs*

It works but does'nt seem to notice the searched for word when it's in a sentence...:question:

Probably just me being pathetic at excel :p

Will have a fiddle about with it...:D


;);)

Hmm.. the EXACT function will only find a 'true' result if the cell contents EXACTLY match the string or value you specify, and it's case-sensitive but ignores formatting differences...

That's about it for my knowledge of Excel tho.. I dunno if you can search in strings of text for specified text, and have that result. You can use the function SEARCH, but it will only locate a character's position in a string and return a number.. as far as I know..

sorry!

[MartinSullivan]

I have a Macro that will delete a row if a user defined column contains a user defined bit of text even if its contained in a string. If I get time Ill play about with it to make it delete rows that dont contain the text, but it will give you a good starting point.

Sub KillRows()

Dim MyRange As Range, DelRange As Range, C As Range
Dim MatchString As String, SearchColumn As String, ActiveColumn As String
Dim FirstAddress As String, NullCheck As String
Dim AC

'Extract active column as text
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)

SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn)

On Error Resume Next
Set MyRange = Columns(SearchColumn)
On Error GoTo 0

'If an invalid range is entered then exit
If MyRange Is Nothing Then Exit Sub

MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
If MatchString = "" Then
NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _
"Type Yes to do so, else code will exit", "Caution", "No")
If NullCheck <> "Yes" Then Exit Sub
End If

Application.ScreenUpdating = False

'to match the WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole)
'to match a PARTIAL text string use this line
Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlPart)
'to match the case and of a WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)

If Not C Is Nothing Then
Set DelRange = C
FirstAddress = C.Address
Do
Set C = MyRange.FindNext(C)
Set DelRange = Union(DelRange, C)
Loop While FirstAddress <> C.Address
End If

'If there are valid matches then delete the rows
If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

[The Dogfather]

Damn MS I was just about to post that ;)

[Burple]

I wasn't about to leap headlong into the arcane and dark art of the Macro ;):p:p

cos I don't know enough about it! :p:D

[N12 JLK]

quote:Originally posted by MartinSullivan

I have a Macro that will delete a row if a user defined column contains a user defined bit of text even if its contained in a string. If I get time Ill play about with it to make it delete rows that dont contain the text, but it will give you a good starting point.

Sub KillRows()

Dim MyRange As Range, DelRange As Range, C As Range
Dim MatchString As String, SearchColumn As String, ActiveColumn As String
Dim FirstAddress As String, NullCheck As String
Dim AC

'Extract active column as text
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)

SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn)

On Error Resume Next
Set MyRange = Columns(SearchColumn)
On Error GoTo 0

'If an invalid range is entered then exit
If MyRange Is Nothing Then Exit Sub

MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
If MatchString = "" Then
NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _
"Type Yes to do so, else code will exit", "Caution", "No")
If NullCheck <> "Yes" Then Exit Sub
End If

Application.ScreenUpdating = False

'to match the WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole)
'to match a PARTIAL text string use this line
Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlPart)
'to match the case and of a WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)

If Not C Is Nothing Then
Set DelRange = C
FirstAddress = C.Address
Do
Set C = MyRange.FindNext(C)
Set DelRange = Union(DelRange, C)
Loop While FirstAddress <> C.Address
End If

'If there are valid matches then delete the rows
If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

Application.ScreenUpdating = True

End Sub





Pardon. Could you repeat that:question::question::question::question::quest ion::question::question::question::question::quest ion::question::question::question::question::quest ion:

[indigomatt]

quote:Originally posted by lesley12


quote:Originally posted by MartinSullivan

I have a Macro that will delete a row if a user defined column contains a user defined bit of text even if its contained in a string. If I get time Ill play about with it to make it delete rows that dont contain the text, but it will give you a good starting point.

Sub KillRows()

Dim MyRange As Range, DelRange As Range, C As Range
Dim MatchString As String, SearchColumn As String, ActiveColumn As String
Dim FirstAddress As String, NullCheck As String
Dim AC

'Extract active column as text
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)

SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn)

On Error Resume Next
Set MyRange = Columns(SearchColumn)
On Error GoTo 0

'If an invalid range is entered then exit
If MyRange Is Nothing Then Exit Sub

MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
If MatchString = "" Then
NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _
"Type Yes to do so, else code will exit", "Caution", "No")
If NullCheck <> "Yes" Then Exit Sub
End If

Application.ScreenUpdating = False

'to match the WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole)
'to match a PARTIAL text string use this line
Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlPart)
'to match the case and of a WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)

If Not C Is Nothing Then
Set DelRange = C
FirstAddress = C.Address
Do
Set C = MyRange.FindNext(C)
Set DelRange = Union(DelRange, C)
Loop While FirstAddress <> C.Address
End If

'If there are valid matches then delete the rows
If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

Application.ScreenUpdating = True

End Sub





Pardon. Could you repeat that:question::question::question::question::quest ion::question::question::question::question::quest ion::question::question::question::question::quest ion:



My head hurts :D:D:D I've no idea what the above is all about :p

Thanks all the same... M

[MartinSullivan]

Its pretty straigtforward - cut it from here (from SUB to ENDSUB), open your excel sheet, press ALT-F11 this opens the visual basic programming interface for excel, Click insert module, paste the code into the new white window. Close the VB interface down and return to your excel sheet. Press ALT F8 and select the macro killrows and select RUN. The macro prompts the user for the row to check and the string to check and deletes the row on a match. Ill play about with it later and get it to do exactly what you want - but have a go at getting the one I have entered already working - and then you will understand a bit more what Im on about.

[indigomatt]

Cheers very much :cool: as I'm sure you've better things to do :D

[MartinSullivan]

quote:Originally posted by indigomatt

Cheers very much :cool: as I'm sure you've better things to do :D


LOL Not really :D

[indigomatt]

Thought as much :p

Managed to run the 'KillRows' macro sucessfully :D

[indigomatt]

Anything yet, you've had all Easter :D:D:D