Excel macro help - checking if boxes on left or right are populated

Question

Hello

I have an application that generates an HTML report that can be opened in Excel. I have written macros to try and format this report so it is sortable and can have filters applied. The length of these reports is impossible to guess as it can be larger some weeks, smaller others.

I am trying to write a vba script macro in order to grab text between slashes. Right now, the problem i am facing is that because the report size (how many rows it will occupy) is still unknown, i am not sure how to write my macro.

Column A is blank. Column B will contain the full path name. IF column a is populated, column c will have the text between the slashes. (For example, column B contains R:\testdocuments\test.html....i want column c to contain testdocuments).

so far the code i have for column c is the following:

Range("C9").Select
ActiveCell.FormulaR1C1 = _
    "=MID(RC[-1], FIND(""\"",RC[-1])+1, FIND(""\"",RC[-1], FIND(""\"",RC[-1])+1)-FIND(""\"",RC[-1])-1)"
Range("C9").Select
    Selection.AutoFill Destination:=Range("C9:C65000"), Type:=xlFillDefault
Range("C9:C20000").Select

As you can tell, this is not effective because it takes forever to sort/filter anything.

Can anybody provide the code that will check if column b is populated and if and ONLY if it is populated, to populate C with the text between slashes for the ability to filter by directory? I am totally foreign to excel-vba and need as much help as i can get.

Thanks in advance.

Answer 1

First of all, a few general tips:

1. When writing macros, you can work with Ranges without selecting them.

2. Use the following code at the beginning of your macro to prevent the application from animating (which slows it down): Application.ScreenUpdating = False

   Edit: Make sure and set it back to 'True' after you're done.

3. Instead of Range("C9:C65000"), you can actually let Excel find the last row for you using Range("A1").SpecialCells(xlCellTypeLastCell).Row.

So, the macro might look something like this:

Sub FillFormulas()
    Dim row As Integer
    Application.ScreenUpdating = False
    row = Range("B9").SpecialCells(xlCellTypeLastCell).row
    Range("C9").Formula = "=MID(B9, FIND(""\"",B9)+1, FIND(""\"",B9, FIND(""\"",B9)+1)-FIND(""\"",B9)-1)"
    Range(Range("C9"), Range("C" & row)).FillDown
End Sub