Previous Topic Next Topic

Excel Macro to find last cell in Sheet

Closed
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010 - Aug 26, 2010 at 01:00 PM
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010 - Aug 30, 2010 at 07:46 AM
Hello,

I'm building a compare using Excel and the macro is going to be used on all sorts of data sizes. Could be 100 rows, could be 2000 rows. I need to build into this macro something that can tell me where to stop. Right now, I have a sheet that is 1140 rows, but have the macro going to 1489. How do I get it to recognize the end of the sheet?

Here is my current macro....

Sub PreparePTCI()
'
' PreparePTCI Macro
' Keyboard Shortcut: Ctrl+Shift+P

Columns("G:I").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 31.29
Selection.NumberFormat = "General"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-5],""-"",RC[-3],""-"",RC[-4])"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H1489"), Type:=xlFillDefault
Range("H2:G1489").Select
Range("H2").Select

' Color Columns

Columns("G:I").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Columns("I:I").Select
Selection.Font.Bold = False
Selection.Font.Bold = True

' Blanks Macro
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""",""Blank"",RC[-1])"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G1489"), Type:=xlFillDefault
Range("G2:G1489").Select
Range("G2").Select

' TECITAB Macro
Sheets("PTCI").Select
Sheets.Add
Sheets("Sheet1").Select
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 3
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "TECI"
Sheets("PTCI").Select

' TECI VLookup

Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],TECI,MATCH(RC[-2],INDEX(TECI,1,),0),0)"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I1489")
Range("I2:I1489").Select

End Sub

4 responses

Answer 1 / 4
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
Aug 27, 2010 at 10:51 AM
I found this code at ozgrid.com, How can I put this code into my code above?

Sub AutoFill()
Dim LastRowInA As Long

Dim r As Range
Set r = Range("d1:e1")

LastRowInA = Range("A65536").End(xlUp).Row

r.AutoFill Destination:=Range("d1:e" & LastRowInA)

End Sub

PLEASE HELP ME! Thanks in advance!!!!!!!!!
0
Answer 2 / 4
Ray
Aug 27, 2010 at 05:54 PM
https://www.ozgrid.com/forum/index.php?thread/90616-center-active-cell-s-contents-across-active-column-and-one-column-over/

Did you see this while you were visiting ozgrid?
0
Answer 3 / 4
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
Aug 30, 2010 at 05:58 AM
I am very new to VBA. Can someone tell me where I put that code into my code shown above?
0
Answer 4 / 4
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
Aug 30, 2010 at 07:46 AM
I figured it out, here is my new code....


Option Explicit

Sub PreparePTCI()
Dim wsNew As Worksheet
Dim LastRow As Long

LastRow = Range("C" & Rows.Count).End(xlUp).Row

With Columns("G:I").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 31.29
Selection.NumberFormat = "General"
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Font.Bold = True

End With

Range("H2:H" & LastRow).FormulaR1C1 = "=RC[-5] & ""-"" & RC[-3] & ""-""& RC[-4]"

Range("G2:G" & LastRow).FormulaR1C1 = "=IF(RC[-1]="""",""Blank"",RC[-1])"

' TECI VLookup
Range("I2:I" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],TECI,MATCH(RC[-2],INDEX(TECI,1,),0),0)"

Set wsNew = Sheets.Add

With wsNew
.Name = "TECI"
.Tab.ColorIndex = 3
End With

End Sub
0