# VBA help



## superfb (Dec 22, 2022)

Hi,

I have a template where the data is added on a quarterly basis.

I was trying to record a macro, but noticed where the last row of data is it selects that particular row not the last row.

for example,


```
Range("A131:N131").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
```

Instead of  Range("A131:N131") how can i incorporate an offset to this?

So irrespective where the row is, it would copy the last row and insert it on a row above?

Thanks


----------



## anand3dinesh (Dec 22, 2022)

superfb said:


> Hi,
> 
> I have a template where the data is added on a quarterly basis.
> 
> ...


In your code, Last row is not dynamic.
replace Range("A131:N131").Select to below code


```
lrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lrow, "N" & lrow).Select
```


----------



## Fluff (Dec 22, 2022)

Another option
	
	
	
	
	
	



```
With Range("A" & Rows.Count).End(xlUp)
      .Resize(, 14).Copy
      .Insert xlDown
   End With
```


----------



## superfb (Dec 22, 2022)

Gents, thank you for the speedy reply!

I should mentioned that the Month (data) is in Column C

The code below works great but it doesnt insert a row on Col A or B ( which is merge)





Fluff said:


> Another option
> 
> 
> 
> ...


----------



## superfb (Dec 22, 2022)

This probably helps alot, due to the offset formula, where Sept is (Column C) i want to copy that row and insert above then enter a formula in the last row like '=IF(C129="Mar", "Jun",IF(C129="Jun", "Sep", IF(C129="Sep","Dec")))

But again that formula is cell specific - be a great help to get some ideas - thanks


----------



## Fluff (Dec 22, 2022)

superfb said:


> it doesnt insert a row on Col A or B ( which is merge)


In that case get rid of the merged cells they are an abomination & should be avoided like the plague.


----------



## superfb (Dec 22, 2022)

Fluff said:


> In that case get rid of the merged cells they are an abomination & should be avoided like the plague.


Totally agree with you but unfortunately i cant 


```
Range("C10").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
```

I tried to incorporate this in the VBA code to avoid the merge cells, so it stops on the last row (whatever row number it is) i just cant think of a way of copying that row and inserting it above!


----------



## Fluff (Dec 22, 2022)

If I merge cols A & B, then run the code I posted the new row is merged & contains the original data.


----------



## superfb (Dec 22, 2022)

Fluff said:


> If I merge cols A & B, then run the code I posted the new row is merged & contains the original data.


It does work, however i have realised it is copying the footnotes at the bottom! So when i change the row to C from A it inserts a row from C and the subsequent columns excluding A & B


----------



## Fluff (Dec 22, 2022)

In that case try
	
	
	
	
	
	



```
With Range("C" & Rows.Count).End(xlUp)
      .Offset(, -2).Resize(, 14).Copy
      .Insert xlDown
   End With
```


----------



## superfb (Dec 22, 2022)

Hi,

I have a template where the data is added on a quarterly basis.

I was trying to record a macro, but noticed where the last row of data is it selects that particular row not the last row.

for example,


```
Range("A131:N131").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
```

Instead of  Range("A131:N131") how can i incorporate an offset to this?

So irrespective where the row is, it would copy the last row and insert it on a row above?

Thanks


----------



## superfb (Dec 22, 2022)

Fluff said:


> In that case try
> 
> 
> 
> ...


This is good progress as it highlights the row" i kept putting -1 in the resize code befor eyou sent this code. Didnt realise it would be offset!

However, i now get a Run-Time error '1004':

This selection isnt valid. Makre sure the copy and past areas dont overlap unless they are the same size and shape

assuming thats because of the merge cells!!



```
Selection.Insert Shift:=xlDown
```

I adjusted the code and this seems to work! Thanks! 

edit: Ah ok - it didnt work


----------



## Fluff (Dec 22, 2022)

How about
	
	
	
	
	
	



```
With Range("C" & Rows.Count).End(xlUp)
      .Offset(, -2).Resize(, 14).Copy
      .Offset(, -2).Insert xlDown
   End With
```


----------



## superfb (Dec 22, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


This worked a treat! I am curious to know by adding another offset line how did that work?

The last thing i wanted to add to this was a formula, where the last row in column c has this formula :'=IF(C129="Mar", "Jun",IF(C129="Jun", "Sep", IF(C129="Sep","Dec")))

But because it is cell specific how can i change it to analyse the row above? incorporate offset to it?



```
With Range("C" & Rows.Count).End(xlUp)
 FormulaR1C1 = "=IF(R[-1]C=""Mar"",""Jun"",IF(R[-1]C=""Jun"",""Sep"",IF(R[-1]C=""Sep"",""Dec"",IF(R[-1]C=""Dec"",""Mar""))))"
  End With
```

I tried to add this but it didnt enter anything?


----------



## Fluff (Dec 22, 2022)

That is a totally different question & so needs a new thread


----------

