0

I'm new to VBA-programming and came to a point, where I'm stuck.. This is probably not too difficult, but i would appreciate any help!

Basic declarations and other stuff:

Dim found As Range
Dim ArtNr As String
Tabelle1.Activate
ArtNr = InputBox("..")
Range("L1") = ArtNr
MsgBox ("..")

Here is the critical code, where I want to find a previously entered value and delete the line in which this value occurs:

EDIT: The line, that is to be deleted is not deleted.

Set found = ActiveSheet.Columns(2).Find _
           (what:=ActiveSheet.Range("L1"), LookIn:=xlValues, lookat:=xlWhole)
If Not found Is Nothing Then found.EntireRow.Delete

End Sub

Any suggestions?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Ka Chiing
  • 17
  • 5
  • 1
    A more [FASTER](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) way. Use Autofilter. – Siddharth Rout Sep 04 '14 at 13:36
  • nope, nothing changed.. it still won't delete this line – Ka Chiing Sep 04 '14 at 13:38
  • It works for me, though I think it'll only find one and only one instance of what is in `Range("L1")`. – Matt Cremeens Sep 04 '14 at 13:40
  • 2
    `lookat:=xlWhole` Maybe because there is an unwanted space. `xlWhole` means you are looking for an exact match. If you are not looking for an exact match then try `lookat:=xlPart` – Siddharth Rout Sep 04 '14 at 13:40
  • @ Thinkincap: yeh I'm searching in Column B with a value that is entered with an InputBox – Ka Chiing Sep 04 '14 at 13:45
  • You are missing an `end if`. – Matt Cremeens Sep 04 '14 at 13:46
  • @ Siddharth Rout: it works with lookat:=xlPart ! how exact is this match? because there are going to be several similar values in this file and i dont want it to delte the wrong one? Thank you! – Ka Chiing Sep 04 '14 at 13:48
  • 1
    The problem is that the values in your cells have spaces or the text which is entered in Inputbox has spaces. You can handle the spaces in the Inputbox by using `Range("L1") = Trim(ArtNr)` But unfortunately you can't handle the spaces in the worksheet. To get an exact match you will have to ensure that the cells in the worksheet do not have spaces. So "Blah" and "Blah " will not be the same – Siddharth Rout Sep 04 '14 at 13:50
  • Mh, alright.. I guess it's not somehow possible to disable the usage of spaces in the worksheet? :D like totally blocking it? Thanks a lot – Ka Chiing Sep 04 '14 at 13:57
  • I wouldn't advice it :) – Siddharth Rout Sep 04 '14 at 13:58
  • So it is possible? ;) Would only be for one sheet.. – Ka Chiing Sep 04 '14 at 13:59
  • Yes it is :) You need to trap the key or in the worksheet change event, replace the SPACE. – Siddharth Rout Sep 04 '14 at 14:01

1 Answers1

0

Range.Find has one of the parameters LookAt whose options can be one of the following enter image description here

Use lookat:=xlPart instead of lookat:=xlWhole

Ravi Yenugu
  • 3,895
  • 5
  • 40
  • 58