Some beginner questions about a macro I'm doing
Posted by Rohan Rangaraj on June 21, 2000 12:29 PM
Howdy folks,
This is one of the greatest, most informative sites I have ever come across, and I'd like the owners to know I appreciate the work you've put into this. I was searching and searching through the site in order to get an answer to my question, but since I have relatively zero programming skills, I haven't been able to combine different codes together to get my macro to work.
The macro is so that we can load a new data set into excel on a daily basis and evaluate our performance, so the size of the data varies.
In any event, the first thing that I wanted to do was to delete certain rows from the data set (all rows beginning with the word "Accrued interest"). So I began recording the macro, placed the cursor in cell A1, went to Edit > Find, typed "accrued interest," and clicked Find Next. Once it made the find, I went to Edit > Delete... > Entire Row, and clicked OK. Then I stopped recording the macro. On its own, when I ran the macro again it worked fine. However, I wanted to loop it, so I went with what MrExcel said in an earlier "Ask MrExcel", and entered the following line as the first line of code:
Do Until ActiveCell.Value = ""
And then I entered the line:
Loop
Just before the final line (End Sub).
When I tried to run this as a loop, it finished the whole process but afterwards gave me the following message:
"Run-time error '91'
Object variable or With block variable not set."
How do I fix that? I know I could just click in column A, sort by name, select all the rows that have Accrued interest and then delete them, but I'd rather enter that into the macro so that someone doesn't have to do that separately every morning.
One other question I have regards copying and pasting a formula all the way down a column. What I want the macro to do is to
Posted by Rohan on June 22, 0100 5:47 AM
Re: Ahhh, I see! There's one glitch in the SEARCH AND DELETE, though
Sweet! Thanks a bunch, Ryan.
Posted by Ryan on June 21, 0100 12:57 PM
' This will count the number of cells in column J that have
' entries and fill down column P starting at P1. This will
' not count the empty cells between entries so if there are it
' will stop before it hits the bottom, so check it. Also included
' is the SearchAndDelete Macro which will delete the lines that
' say Accrued interest, and stop when there are no more. Hope you like it, let me know!
Ryan
Sub FillColumnP()
Dim Entries As Integer
Application.ScreenUpdating = False
Entries = Application.WorksheetFunction.CountA(Range("J:J"))
Range("P1:P" & Entries).FillDown
Application.ScreenUpdating = True
End Sub
Sub SearchAndDelete()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Do
Cells.Find(What:="Accrued Interest", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Delete Shift:=xlUp
Loop
ErrHandler:
Application.ScreenUpdating = True
Exit Sub
End Sub
Posted by Rohan Rangaraj on June 21, 0100 1:41 PM
Ryan,
Thanks a bunch for the code! I appreciate it.
Ok, this is probably going to be a dumb question, but before I implement the code I was just wondering: Why is it going to count up the number of entries in column J, before deleting the rows from P?
Just wondering, because for me, instead of deleting from a Column P, it will find "accrued interest" in Column A, so I was wondering if it still needs to count up the entries in J.
Thanks a bunch.
Ro
PS - If it's not too much to ask, do you also know how I could get Excel to change subtotal lines into bold? I calculate the subtotal in the macro, then collapse the spreadsheet and go back and individually bold each line, but that becomes line-specific as well and does not translate across new data sets. Thanks!
Posted by Ryan on June 21, 0100 2:44 PM
The two macros are two different ones. You stated that you go through and delete the rows with Accrued interest, so i made a macro for that. You also said you need to copy a formula down column P for how many entries there were in column J, so i made a macro to do that. There are two seperate ones. Sorry to confuse you.
As for the bolding problem, I would have to see the could to see if it could be done, which I'm sure it can, but just need to see specifics.
Ryan
Posted by Rohan on June 21, 0100 2:52 PM
Ahhh, I see! There's one glitch in the SEARCH AND DELETE, though
Ryan,
No need to apologize, my man! That was exactly what I needed, except for one thing - rather than deleting the single cell and shifting up, I'd like it to be able to Delete the ENTIRE ROW. How would I implement that into the code?
Thanks,
Ro
Posted by Ryan on June 21, 0100 3:47 PM
Re: Ahhh, I see! There's one glitch in the SEARCH AND DELETE, though
Sorry about that, i didn't even check to make sure it deleted the whole line. This will take care of it though.
Sub SearchAndDelete()
Dim Row As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Do
Row = Cells.Find(What:="Accrued Interest", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Row
Rows(Row).Delete Shift:=xlUp
Loop
ErrHandler:
Application.ScreenUpdating = True
Exit Sub
End Sub
