How to set range in VBA as active cell

[nalle077]

Hello an thank you for a great forum that has helped me alot.
I have a macro that looks almost exatly like the one in this thread https://www.mrexcel.com/forum/excel-questions/793062-modify-vba-suit.html
I just want the range to be the column that I am currently in.
How to do this?

Example of files that I want to change.
Sometimes the number is in another column C, D, E or whatever.
So I want to place the marker in a cell in that column,
and use the macro to make empty rows acording to the info in the column with the number.

Column A Column B Column C
Hello 5
MrExcel 4
How 5
Are 3
You 4


The rows with Range "B" is what I mean.

Dim lngStartRow As Long
Dim lngEndRow As Long
Dim m As Integer
Dim n As Long


With ActiveSheet
'work from bottom up
'set start row
lngStartRow = Range("B" & CStr(Application.Rows.Count)).End(xlUp).Row
'set end row
lngEndRow = 1

'loop through all used rows in column B starting at bottom
For n = lngStartRow To lngEndRow Step -1
If IsNumeric(Range("B" & CStr).Value) Then
'insert rows below
For m = 2 To Range("B" & CStr).Value
Rows(n + 1).Insert
Next m
End If
Next n
End With
End Sub

 

[nalle077]

Hm the example did not look as I was expecting .
The text is in column A and the number is in column B or another.

 

[JackDanIce]

Try:

Sub macro1()

    Dim LR  As Long: LR = Cells(Rows.Count, 1).End(xlUp).Row
    Dim x   As Long
    Dim y   As Variant
    
    Application.ScreenUpdating = False
    
    For x = LR To 1 Step -1
        y = Cells(x, Columns.Count).End(xlToLeft).Value
        If IsNumeric(y) Then Cells(x + 1, 1).Resize(Application.Max(1, y - 1)).EntireRow.Insert
    Next x
    
    Application.ScreenUpdating = True
    
End Sub

 

[nalle077]

That was brilliant, Thank you so much.
Maby I should have mention this from the beginning, but my next step in this macro is to fill the empty cells with the info from above.
I do this by using F5, Special, Empty cells, =A1, ctrl+Enter, mark all, copy, paste values
only problem with this is if there are this 5 examplecolumns and some rows has no information in the table
Hello, How, Are, You, 5
Hello, How, , You, 4
Hello, How, Are, , 3
Hello, , Are, You, 5
Do you also have a sollution for filling the cells like I discribe?

 

[JackDanIce]

You're welcome, glad it helped.

Your first request appears to be satisfied, however, technically this is a separate and new ask (filling blank cells) which per board rules, should be new thread.

Have you tried to record a macro and adjust it to fit your needs?
Have you searched online for questions like this asked before?

Your solution is not clear either, unfortunately it's difficult to understand what you mean, given you can see your spreadsheet and layout but the example doesn't seem relevant.

 

[nalle077]

Hello again. Ok I will start a new thread, I think the sollution would be something mixed in the macro, that was why I continue in this thread.
I will start a new thread if I do not solve it on my own.
Best regards