VBA question from a novice - finding and range naming

[MotoMoto]

Hi there

I have written a few macros by recording the actions and all has been good, until now.

I have a table of data which has date and time in column A, room numbers in column B and call type in column C. Column D calculates the time elapsed from the previous call type.

I am trying to do a couple of things which I can happily do manually, but when I record the steps it does not work in the macro.

For example, I want to create a range called Time which is the data in column D and the range need to start at row2 and finish one row below the last row with data in it (ie it needs to be dynamic as this will change each time I copy in data). Manually I would go to cell D2, press Shift-Control-Down (takes me to last entry), then do Shift-Down and then name the range. If I record this, it puts the absolute row number in the macro which is no good.

My other query is that I need to look at column C, find the occurence of a certain call type and then insert an entire row at each occurence. Manually I do this by highlighting column C, press CtrlF, type in the word I am searching for, select find all, scroll to the end of the list that it shows and hold the shift key while highlighting the last entry. Then I close the box, do Ctrl+ and select entire row to insert. Again, recording this action just does not work. It also needs to just let me know if it cannot find any occurences of that call type, rather than giving me an error message.

Apologies if I have not explained this very well but I am a real novice at macros.

Any help would be much appreciated.

 

[Fazza]

does this suit?

'if the data is in one contiguous block A1:Dz,
'and you want the next row beyond that block
Range("A1").CurrentRegion.Resize(, 1).Offset(1, 3).Name = "Time"
'offset one row from block and three columns (from A to D), resize to one column

for the second question, please provide an example.

 

[MotoMoto]

Hi Fazza

Thank you so much for your reply. Here is a sample of some data:

Time	Room No	Action	Time From Previous Action	Total Per Call
18/02/2020 06:42​	ROOM 12	Call A	00:00:00​
18/02/2020 06:47​	ROOM 12	Response 1	00:04:40​
18/02/2020 07:43​	ROOM 12	Call A	00:00:00​
18/02/2020 07:44​	ROOM 12	Response 1	00:01:10​
18/02/2020 07:45​	ROOM 12	Call B	00:00:00​
18/02/2020 07:45​	ROOM 12	Reset	00:00:10​
18/02/2020 09:11​	ROOM 12	Call B	00:00:00​
18/02/2020 09:25​	ROOM 12	Reset	00:13:32​
18/02/2020 08:47​	ROOM 18	Call D	00:00:00​
18/02/2020 08:51​	ROOM 18	Reset	00:04:10​

For obvious reasons I have kept the information vague.

The first thing I want to do is is create a range called Time which covers column D (Time from previous action) and starts from the first row of data and finishes 1 row below the last row of data. It will always be column D but the number of rows may change as different data is copied into this spreadsheet. Do you think your macro will do this Fazza, as it mentions column A?

To explain the second request, I have 4 types of calls (A,B,C and D) and I need to find each one at a time and insert an entire row on each occurance thereby seperating them from the data above by one row. My first problem is that not all four call types might be present in the data set each time, so I have to make sure the macro does not return an error. I have given you a simplified example above, sometimes there are a number of responses and so I cannot simply insert a blank row every two lines as the example above might suggest. As I said I can do this manually in the way I described but the macro does not record the keystrokes so I am a bit stuck.

Any help is gratefully received - I will let you know if it helps.

Thank you

 

[MotoMoto]

Hi Fazza

I have just used the VBA you gave me for the range name and it works perfectly, thank you so much.

If someone can help with the second part that would be great.

I cannot tell you how valueable it is for me as an interested beginner to come to a site like this and learn from people who are so knowledgable.

Thank you for giving up your time.

 

[MotoMoto]

I have looked ahead to my next step (once blank rows have been inserted) which is to to highlight column D, select and finds all blanks and then autosum. When I do this manually the blank cells in column D have a sum in it which adds up the values from the row after last blank cell to the one above this one. It works perfectly and the cell has the formula so that if I change a value it will change the total.

When I record the steps for a macro, the sums do not work. The first sum cell says =sum(D5:D1048564) instead of =sum(D2:D5).

Here is my recording steps in case you see something obvious:

I select the range called "Time" which highlights column D from row 2 to the last row with data + 1 (so I have a blank cell for the lest sum).
I click Find and Select >GoTo Special>and click on blanks (now all my blank cells in column D are highlighted)
I then select AutoSum and voila, I have totals for the data between the blank cells.

I am sure it is the autosum which is not quite right in my macro but I dont know how to correct it.

As always, any help is gladly received.

 

[MotoMoto]

I did find this VBA online which gives me a total but it is an absolute figure, not a sum, so will not change if I tweak a figure in the range:

lastrow = Cells(Rows.Count, "D").End(xlUp).Row
firstrow = 2
TempTotal = 0
For x = firstrow To lastrow + 1
If Cells(x, "D") <> "" Then
TempTotal = TempTotal + Cells(x, "D")
Else: Cells(x, "D") = TempTotal
TempTotal = 0
End If
Next x

Sorry, I couldnt see a way of editing my previous post to add this.

 

[MotoMoto]

So, I have trawled through the internet and managed to solve all of my queries bar one (I think, and hope! - I will double test with live data another day).

I am now only stuck with finding a VBA which will look down column C, find each occurance of 4 different words and insert an entire blank row above each occurance. The following works fine for one of the words, but when I just copy it into a new macro and change the text that it is looking for to the second word it doesnt work - maybe the blank rows already inserted play a part in that?

Sub Step3a_InsertBlankRowBeforeTextA()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long

Col = "C"
StartRow = 3
BlankRows = 1

LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "TextA" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub

The aim is to start at row 3 of column C, work down and, everytime it finds one of fours key words, insert an entire row above that word. Maybe this macro can be adapted to search for all 4 text strings at once?

Many thanks for any clues from a bleary eyed, brain frazzled VBA newbie.

 

[Fazza]

Yes, when new to VBA it can take a long, long time to get the code just right. With much practice it becomes easy. All the best

    Const lROW_FIRST_TO_CHECK As Long = 3
    Const sCOLUMN_TO_CHECK As String = "C"
    Dim iRow As Long
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'Usually best to loop from last row when inserting or deleting rows
    For iRow = Cells(Rows.Count, sCOLUMN_TO_CHECK).End(xlUp).Row To lROW_FIRST_TO_CHECK Step -1
        Select Case Cells(iRow, sCOLUMN_TO_CHECK).Value
            Case "Call A", "Call B", "Call C", "Call D": Rows(iRow + 1).Insert
        End Select
    Next iRow
    
    Application.EnableEvents = True