vba Found text cell address as last row range row

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
Folks:

Looking to modify your code to find the row with "Grand Total" in column B, then
Code:
Range("A2:H" & LstRow).Copy


I tried this code, it only copied 2 rows, definitely not the row with "Grand Total" found in it.

How can I fix it?

Thank you, Rowland:


Code:
Sub try_this()
    
'copy
        Workbooks("United States (de linked)xxxx.xlsm").Activate
            Sheets("500-Sample").Activate
    
    'Expand Column groups, Collapse Row groups
                ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
  
          
                'contiguous method won't work:
          'Range("A3").Select
          'Range(Selection, Selection.End(xlDown)).Select
          'Range(Selection, Selection.End(xlToRight)).Select
          
        LstRow = Application.WorksheetFunction.Match("Grand Total", ActiveSheet.Range("B:B"))
            Range("A2:H" & LstRow).Copy
'paste
        Workbooks("Incoming_Data.xlsm").Activate
          Sheets("Master-Incoming").Activate
          Range("B65000").Select
          Selection.End(xlUp).Select
          ActiveCell.Offset(1, -1).Select
          
                Selection.PasteSpecial Paste:=xlPasteValues, _
          Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Selection.PasteSpecial Paste:=xlPasteFormats, _
          Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
          Workbooks("United States (de linked)xxxx.xlsm").Activate
          
                'These two maybe not necessary bc don't save this wb:
          Application.CutCopyMode = False
          Range("B1").Select
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Folks:

Forgot I had this method from a macro I used a couple of years ago. Only really finds last cell, not last cell with specific text:

Code:
'Determine last copy row of sheet<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
                Range("p1").Select<o:p></o:p>
                ActiveCell.Formula = "=(65536-COUNTBLANK(A:A)-4)"<o:p></o:p>
                RowCount = ActiveCell.Value<o:p></o:p>
                If RowCount = "0" Then<o:p></o:p>
                Else<o:p></o:p>
                    'ActiveWorkbook.Unprotect "swordfish" password specific<o:p></o:p>
                    'Copy<o:p></o:p>
                    Range(Cells(10, 1), Cells(RowCount + 9, 15)).Copy<o:p></o:p>
 
Last edited:
Upvote 0
Folks:

I'm looking for the first (and only) "Grand Total" in column B. There are populated cells underneath the "Grand Total" so row counting is not enough. I can add a formula If I find that Grand Total, offset 1 row down and make that cell = row formula for the found cell:
Code:
    Range("B1").Select
    Columns("B:B").Select
    Selection.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(1, 0).Activate
    ActiveCell.NumberFormat = "General"
    ActiveCell.Formula = "=ROW(R[-1]C)"
    ActiveCell.Copy
 
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Was trying to do this without relying on cell formulas in case the source doc changes and the cells I'm typing in are now in use or locked.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

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
Back
Top