When I am debugging a excel-vba program I came to know my data range is not completely selected.
Below picture shows my data's model and my problem.
I used this code to select the whole range. But this is not working properly.
Dim rngTemp As Range
Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
With rngTemp
Please help me by giving the code for selecting the whole range as given in the figure above.
In your code you are searching by xlByRows. And hence you are getting the address of the last cell which has data which is G7.
Further to my comment, Is this what you are trying?
Sub Sample()
Dim lastrow As Long, lastcol As Long
Dim rng As Range
With Sheets("Sheet1") '<~~ Change this to the relevant sheet
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lastcol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
lastrow = 1: lastcol = 1
End If
Set rng = .Range("A1:" & _
Split(.Cells(, lastcol).Address, "$")(1) & _
lastrow)
MsgBox rng.Address
End With
End Sub
PLEASE BE AWARE THAT METHOD BELOW IS NOT RELIABLE IN SOME CASES. I WILL LEAVE THIS ANSWER HERE AS A BAD EXAMPLE. FOR DETAILED INFORMATION PLEASE SEE @SiddharthRout 'S EXPLANATION IN THIS LINK
I would use following code to find used range instead of looking for "*" in the cell value:
Sub SelectRange()
Dim LastRow As Long
Dim LastColumn As Long
Dim aWB As Workbook
Dim aWS As Worksheet
Set aWB = ActiveWorkbook
Set aWS = aWB.ActiveSheet '<-You can change sheet name like aWB.sheets("SheetName")
With aWS.UsedRange
LastRow = .Rows(.Rows.Count).Row
LastColumn = .Columns(.Columns.Count).Column
End With
aWS.Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select '<---Cells(1, 1) is the starting cell of range)
End Sub
