54475

How to select the lastrow

Question:

I have an excel sheet it contains data as below:

A B C D E F 1 10 11 12 78 45 2 12 15 15 78 45 3 17 18 13 7 45 4 12 45 7 78 78 5 578 54 45 8 78 6 42 72 75 8 78 7 452 22252 2277 87 986 8 752 72 752 878 98638 9 72 72 72 45 78 10 788 72 78 678 465

Now, I want to select the last row in this excel. Because everyday the row number will be change.<br /> But I don't want the Selection.End(xlToRight).Select because in this excel blanks will come after every 2 columns.<br /> Ex:

I want to select A10 to last cell in this row.

I have create “usedrange” method but it doesn’t work well for me.

startcell = Range("B" & Cells.Rows.Count).End(xlUp).Select endrcell = Range("G" & Cells.Rows.Count).End(xlUp).Select

How can I select the startrow to endcell with blanks?

Please advice.

Answer1:

To <strong>Select</strong> the last row with data, first move upwards from the bottom to find the row and then move leftwards on that row to find that last used column:

Sub SelectLastRow() Dim nRow As Long, nColumn As Long nRow = Cells(Rows.Count, "A").End(xlUp).Row nColumn = Cells(nRow, Columns.Count).End(xlToLeft).Column Range(Cells(nRow, "A"), Cells(nRow, nColumn)).Select End Sub

Answer2:

How about this:

Sub GetLastRow() Dim rng As Range, lastRow As Long, col As Long lastRow = Range("A1").End(xlDown).Row //Get last row in column A col = Range("XFD" & lastRow).End(xlToLeft).Column //Get last used column in last row Set rng = Range(Cells(lastRow, 1), Cells(lastRow, col)) End Sub

Recommend

  • VBA code that finds first row in column with integers and stores rownum as variable
  • Calculating sum and grouping of dynamic range
  • If value will change, change few cells color
  • Cannot copy to the last cell in the range
  • Pick folder routine going to Error handler - Excel VBA
  • Excel vba calculate average of a column
  • Enter a new line and copy formula from cells above
  • Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA
  • Excel VBA delete entire row if both columns B and C are blank
  • Loop paste formula until next cell in range is empty
  • vba userform , if any of the checkboxes in the frame is true then macro should not be applied on the
  • VBA Multiple loops match conditions
  • VBA: How can I keep only the date values from a string?
  • Slow Performance When Reading Excel With Microsoft.office.Interop.Excel
  • VBA Borders Excel Dependent on Page Size
  • vba paste values and keep source formatting?
  • Erase empty cells in an array
  • Delete All Rows With Errors in a column
  • Double-click autofill - dynamic based on adjacent cell
  • Using Sax parsing to edit and write XML in VB6
  • Excel's Macro-Recorder usage
  • custom UITableViewCell with image for highlighting
  • Django: Count of Group Elements
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • How to handle AllServersUnavailable Exception
  • Trying to switch camera back to front but getting exception
  • vba code to select only visible cells in specific column except heading
  • VBA Convert delimiter text file to Excel
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Error creating VM instance in Google Compute Engine
  • Free memory of cv::Mat loaded using FileStorage API
  • Hits per day in Google Big Query
  • Angular 2 constructor injection vs direct access
  • how does django model after text[] in postgresql [duplicate]
  • Programmatically clearing map cache
  • Reading document lines to the user (python)
  • How to load view controller without button in storyboard?