Excel VBA code to select non empty cells

vba excel spreadsheet
k.dkhk picture k.dkhk · Oct 10, 2016 · Viewed 46.4k times · Source

In Excel, let's I have data in B2 to B7 and C2 to C7 . In VBA I can write a macro to select it: 

Sub Macro1()
Range("B2:C7").Select
End Sub

How do I rewrite the code so that it chooses automatically the cells that are non-empty? If I delete the data in cell B7 and C7 then I want the macro to select only Range(B2:C6) And if I add data to Cell B8 and C8 then I want the macro to choose Range(B2:C8).

My data will always start a B2,C2 and I will not have any free space between data.

Answer

L.Dutch - Reinstate Monica picture L.Dutch - Reinstate Monica · Oct 11, 2016

your data always start at B2,C2 and has no empty cell inbetween? If so you can set a variable to be the "last filled in row" 

lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("B2:C" & lastRow).Select

and define the range from B2 to the C"last row"

Related questions

Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

We need to write an Excel spreadsheet with VBA code in it; the code reads and performs operations on the data in the first worksheet. The user will be receiving spreadsheets containing data but that do not contain the VBA …

Read More
Selecting non-blank cells in Excel with VBA

I'm just beginning to dive into VBA and I've hit a bit of a roadblock. I have a sheet with 50+ columns, 900+ rows of data. I need to reformat about 10 of those columns and stick them in a new workbook. How …

Read More
What is the difference between Sheets.Select and Sheets.Activate?

In VBA for Excel, what is the difference between Sheets.Select and Sheets.Activate ?

Read More