vba to drag down formula/data from last filled row to next row

Deepas

New Member
Joined
Mar 2, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
hello, i am a complete novice to macros.

Request you help on the below.

i need to go the last filled cell in column A
select the data in that entire row (currently in my sheet it is "A1312:AP1312")
and drag it it down to the next row
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,255
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub deepas()
   Range("A" & Rows.Count).End(xlUp).EntireRow.Resize(2).FillDown
End Sub
 
Solution

Deepas

New Member
Joined
Mar 2, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Range("A" & Rows.Count).End(xlUp).EntireRow.Resize(2).FillDown End Sub
thankyou so much, this was of great help. also i need to repeat this for 27 other tabs (there are 30 tabs in total). is there a macro for that as well
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,255
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub deepas()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Select Case Ws.Name
         Case "Sheet1", "Sheet2", "Sheet3"
         Case Else
            Ws.Range("A" & Rows.Count).End(xlUp).EntireRow.Resize(2).FillDown
      End Select
   Next Ws
End Sub
Change the 3 sheet names to the sheets that should not be touched.
 

Deepas

New Member
Joined
Mar 2, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub deepas()
   Dim Ws As Worksheet
  
   For Each Ws In Worksheets
      Select Case Ws.Name
         Case "Sheet1", "Sheet2", "Sheet3"
         Case Else
            Ws.Range("A" & Rows.Count).End(xlUp).EntireRow.Resize(2).FillDown
      End Select
   Next Ws
End Sub
Change the 3 sheet names to the sheets that should not be touched.
Thankyou so much. this was of great help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,255
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Deepas

New Member
Joined
Mar 2, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi & welcome to MrExcel.
How about
VBA Code:
Sub deepas()
   Range("A" & Rows.Count).End(xlUp).EntireRow.Resize(2).FillDown
End Sub
what if i just want to drag down the formula in the last cell of column A to cell A 5000
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,255
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Sub deepas()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Select Case Ws.Name
         Case "Sheet1", "Sheet2", "Sheet3"
         Case Else
            With Ws.Range("A" & Rows.Count).End(xlUp)
               .EntireRow.Resize(5000 - .Row + 1).FillDown
            End With
      End Select
   Next Ws
End Sub
 

Deepas

New Member
Joined
Mar 2, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Dim Ws As Worksheet For Each Ws In Worksheets Select Case Ws.Name Case "Sheet1", "Sheet2", "Sheet3" Case Else With Ws.Range("A" & Rows.Count).End(xlUp) .EntireRow.Resize(5000 - .Row + 1).FillDown End With End Select Next Ws
no i only want to drag down the data in column A and not the entire row.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,255
Office Version
  1. 365
Platform
  1. Windows
In that case get rid of the .EntireRow
 

Watch MrExcel Video

Forum statistics

Threads
1,132,980
Messages
5,656,203
Members
418,289
Latest member
Kjohno

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top