Autofit non-specific cell columns Excel VBA

Question

So I am new to Excel/VBA and was advised to try and take on a stretch task and automate a process. I need to grab every Ticket ID on sheet 1 (one at a time) and check if it's in the second sheet. Then same process for sheet2 comparing sheet1. Ticket IDs are on column 2 of the sheets. If I don't find the Ticket ID on the other sheet, I need to add that entire row of the ticket ID to the third sheet.

Dim i As Integer, TicketId As Range, CellId As Range
Dim SearchRange1 As Range, SearchRange2 As Range
Dim rowCount1 As Integer, rowCount2 As Integer, x As Integer
x = 1

'Get row counts from both sheets and set search ranges##
Sheets("Sheet1").Select
rowCount1 = Range("A2", Range("A2").End(xlDown)).Count
Set SearchRange1 = Range("B2", Range("B2").End(xlDown))

Sheets("Sheet2").Select
rowCount2 = Range("A2", Range("A2").End(xlDown)).Count
Set SearchRange2 = Range("B2", Range("B2").End(xlDown))

'loop to compare differences in sheet1 to sheet2 and add to third sheet##
For i = 2 To rowCount1
    Sheets("Sheet1").Select
    Set TicketId = Cells(i, 2)
    Set CellId = SearchRange2.Find(What:=TicketId)
    If CellId Is Nothing Then
        Rows(i).Select
        Selection.Copy
        Sheets("Sheet3").Select
        Cells(x, 1).Select
        Cells(x, 1).PasteSpecial
        Rows(x).Select
        Selection.Columns.AutoFit
                    'Cells(x, 1).Value = TicketId.Value##
        x = x + 1
    End If
Next i
'Sheets("Sheet2").Select##

Application.CutCopyMode = False

My issue is that when I get the data into the third sheet, I can't get the columns to fit correctly and I can't see all the data. I researched and found the AutoFit command but I don't know how to implement it in my code. I've seen other implementations of AutoFit but the coder always knows the range he's using specifically. I don't know the row I'm putting the data on. I've included one of the many implementations I've tried: selecting the row, and autofitting my selection. Suggestions? As a side note, my pasting method is also the only one that would work for me, I attempted to use pastespecial with :=xlPasteValues and :=xlPasteFormats but it didn't work either.

Rows(x).Select |Selection.Columns.AutoFit you select row then tries to autofit column.... which is it??? Edit: also, Batman will link you to avoid using select. — findwindow
I need to Autofit the entire row's columns. that was just one of many things I've tried. I don't know where to go from this point. I'm new to this language, I started this project 2 days ago and knew nothing about vba. I apologize if the code is sloppy and inefficient. — rdan1
lol of course Batman is out fighting crime. That's good for 2 days ^_^ — findwindow
How can I make my code more efficient? I know you said to avoid using select. What should I use instead? — rdan1
You would work directly with the cells. @bruce wayne has the link. — findwindow

Gary Evans Gary Evans · Accepted Answer · 2016-06-21T19:05:23

If you are unsure of the range, consider using AutoFit on the whole worksheet.

ThisWorkbook.Worksheets("Sheet3").columns.autofit

Autofit non-specific cell columns Excel VBA

1 Answers