Selection Macro not working...

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
  1. 365
  2. 2019
I have this code to select a table without selecting the headers... it comes up with an error...

Rich (BB code):
Sub zselect()

    Dim lastrow As Integer
    lastrow = Range("A65536").End(xlUp).Row
    Dim lastcol As Integer
    lastcol = Range("AA2").End(xlToLeft).Column
    
    Range("A1:" & lastcol & lastrow).Select

End Sub

I just figured out that lascol is a number... is there any way i could bring it back as the column letter?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Where you are dealing with column numbers it is easier to use Cells to define the range

Code:
Sub zselect()
    Dim lastrow As Long, lastcol As Integer
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    lastcol = Range("AA2").End(xlToLeft).Column
    Range(Cells(1, 1), Cells(lastrow, lastcol)).Select
End Sub

Why do you need to Select this range - it is rarely necessary to select anything in order to work with it.
 

sgdev

New Member
Joined
Jun 25, 2008
Messages
11
try this instead - let me know if it help...

Sheet1.Range("b2", Sheet1.Range("b2").SpecialCells(xlCellTypeLastCell)).Select

i've assumed that:
sheet1 is the name of your worksheet
b2 is the first cell of your data

ta
 

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
  1. 365
  2. 2019
It's part of a macro that will copy the table data to another workbook from cells A2 - end of table to cells F15 on another worksheet. I figured this is the best way, and i don't want to copy the header row (A1-c1) because the titles are different in the new wb AND the destination cell changes with different spreadsheets i open.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try this

Code:
Sub zselect()
Dim lastrow As Long, lastcol As Integer
With Sheets("Sheet1")
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    lastcol = .Range("AA2").End(xlToLeft).Column
    .Range(.Cells(2, 1), .Cells(lastrow, lastcol)).Copy Destination:=Sheets("Sheet2").Range("F15")
End With
Application.CutCopyMode = False
End Sub
 

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
  1. 365
  2. 2019
Hmm, it's kind of what i need.. except that i want it to search for a specific word and paste the values under that cell.

RemAccount is the value of the cell

It's unique and found nowhere else in sheet 2

The cell is not always going to be "F15", it may change, so, searching for RemAccount is the best option because it is the first heading in the new table.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub zselect()
Dim lastrow As Long, lastcol As Integer, Found As Range
Set Found = Sheets("Sheet2").Cells.Find(what:="RemAccount")
If Found Is Nothing Then
    MsgBox "RemAccount not found."
    Exit Sub
End If
With Sheets("Sheet1")
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    lastcol = .Range("AA2").End(xlToLeft).Column
    .Range(.Cells(2, 1), .Cells(lastrow, lastcol)).Copy Destination:=Found.Offset(1, 0)
End With
Application.CutCopyMode = False
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

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
Top