Excel Macro to find last cell in SheetClosed

Question

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 

Configuration: Windows XP / Internet Explorer 7.0

DCecil07 · Answer

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!!!!!!!!!

Ray · Answer

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?

DCecil07 · Answer

I am very new to VBA.  Can someone tell me where I put that code into my code shown above?

DCecil07 · Answer

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

Excel Macro to find last cell in Sheet

4 replies

Similar discussions

Subscribe To Our Newsletter!