Previous Topic Next Topic

Help making macro finds last used line + push down and delete !

Closed
Karina-Brazil Posts 1 Registration date Thursday November 13, 2014 Status Member Last seen November 13, 2014 - Nov 13, 2014 at 06:28 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 15, 2014 at 01:55 AM
Hello guys,

im always here to find answer for my excel problems but couldnt find answer for this.

Every single day i have to do a job that is consuming my time and i would like to know if i can automize this.


But what I actualy want is that the macro finds the last row that is used (in this case, the last row that has data is 139) and push the selected row (139) to the next row (140). Im talking about date data, so it cant be copy-paste.

After that I need that the macro automatic ereases the cells I, J, E, and C that has been created at last (in this case on line 140). In other words, it must recognize what is the last row (that has just been created) and delete the cells specified

The macro is this one. (obviously with selection, since im selecting everything manually)

Range("B139:J139").Select
Selection.AutoFill Destination:=Range("B139:J140"), Type:=xlFillDefault
Range("B139:J140").Select
Range("I140").Select
Selection.ClearContents
Range("J140").Select
Selection.ClearContents
Range("E140").Select
Selection.ClearContents
Range("C140").Select
Selection.ClearContents

I tried using the lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row and other command many times and i cant get it work. i think im to dumm for this.

can someone give me a light? im willing to donate (paypal) if someone can help me getting this done, since is pissing me off. Sorry if is not that clear, english is not my main language.

Thanks everyone

Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 15, 2014 at 01:55 AM
you should have given a small extract of your data. see the saample data below in sheet1. as you are messing up with data copy sheet1 to shee2 before using the macro . the macro is given below. invoke only "test"



1 x x x x x x x x x
2 x x x x x x x x x
3 x x x x x x x x x
4 x x x x x x x x x
5 x c x e x x x i j 



Sub test()
Dim lastrow As Long, r(1 To 4), j As Long
undo
Worksheets("sheet1").Activate
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastrow, 1).EntireRow.Insert shift:=xlDown
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set r(1) = Cells(lastrow, "I")
Set r(2) = Cells(lastrow, "J")
Set r(3) = Cells(lastrow, "E")
Set r(4) = Cells(lastrow, "C")
For j = 1 To 4
r(j).Clear
Next j
MsgBox "macro done"
End Sub


Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")
End Sub
0

Similar discussions

Should I delete my TikTok account and start over?
Frznrth -
najaf123 -

5 responses