Sarah has attended:
Excel VBA Intro Intermediate course
Access Advanced course
Access Intermediate course
Delete cells macro
Is it possible to write some code that looks at a specific range (K1:AM2104) and if it finds "65-4" in any of the cells, it then deletes this cell and the one cell below it and then shift all the cells on the right of these 2 cells left?
RE: Delete cells macro
Hi Sarah, thanks for your query. The following code should do the trick. I named my source datasheet "mydata", you should amend this to your own source datasheet name. Here's the code:
*****
sub delete_me()
Dim introwcount As Integer
Dim intcolumncount As Integer
For introwcount = 1 To Sheets("mydata").Range("K1:AM2104").CurrentRegion.Rows.Count
For intcolumncount = 1 To Sheets("mydata").Range("K1:AM2104").CurrentRegion.Columns.Count
If Sheets("mydata").Range("K1").Cells(introwcount, intcolumncount).Value = "65-4" Then
Range(Sheets("mydata").Range("K1").Cells(introwcount, intcolumncount), Sheets("mydata").Range("K1").Cells(introwcount + 1, intcolumncount)).Select
Selection.Delete Shift:=xlToLeft
End If
Next intcolumncount
Next introwcount
End Sub
*****
Hope this helps,
Anthony
RE: Delete cells macro
Hi Anthony,
I've just tried this but its not deleting all of the cells that have "65-4" in the selected range.
Its only deleting a couple of cells.
Any ideas?
Thanks,
Sarah
RE: Delete cells macro
Hi Sarah. Can you check whether you have any trailing spaces after each 65-4 or if they are any spaces on either side of the hyphen?
Anthony
RE: Delete cells macro
Hi,
There are no spaces either side of the hyphen or trailing spaces.
Sarah
RE: Delete cells macro
Hi Sarah, that's very odd and very irritating! I've run a test on dummy data here and all instances of the search term are found and removed. I can't help you much further without examining the spreadsheet itself, I'm afraid - although my first port of call would be to check the number formatting on the cells which aren't being deleted. As you can see from the code, your hyphenated search term is hard-coded in so it should be picking up all exact matches, indicating those values not being picked up are different in some way. Try altering the code to run on a different range of data and see what happens. After that, we could possibly arrange a time for you to email the spreadsheet to us, if needs be.
Anthony
