Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Delete cells macro
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Delete cells macro
Resolved · Low Priority · Version 2007
Reply Wed 5 Jan 2011, 11:53Delegate Sarah said...
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?
For upcoming training course dates see: Pricing & availability
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
Reply Wed 12 Jan 2011, 15:42Delegate Sarah said...
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
Reply Thu 13 Jan 2011, 13:44Delegate Sarah said...
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
Reply Thu 13 Jan 2011, 21:41Delegate Sarah said...
RE: Delete cells macro
Hi,
I'll give this a go tomorrow on a test sheet and I'll get back to you.
Thanks,
Sarah
|