VBA Code to Autosize cells

[sealion1]

Hi all,

Could someone possibly tell me the code to autosize the cells on my "dashboard" tab.

Whenever, I am pasting new data to the dashboard, the cells do not resize to show all the data.

Any help is appreciated.

Thanks

 

[Deepak]

Do you mean by "AutoFit".
Pls share the sample.

 

[sealion1]

Hi Deepak,

Yes, I mean "autofit" - currently, as the data pulls through the cell don't resize automatically so you can't see all the data

My current code is as follows:

Sub somesub()
Dim LastRow As Long, c As Range
Dim LastRow1 As Long, LastRow2 As Long
Dim MyRange As Range, ChkRange As Range
Dim Src As Worksheet, d As Range
Dim Dst As Worksheet
Set Src = Sheets("Raw Data")
Set Dst = Sheets("Dashboard")
LastRow1 = Sheets("Keywords").Cells(Rows.Count, "A").End(xlUp).Row
Set ChkRange = Sheets("Keywords").Range("A2:A" & LastRow1)
LastRow = Src.Cells(Rows.Count, "AI").End(xlUp).Row
Set MyRange = Src.Range("AI2:AI" & LastRow)
For Each c In MyRange
For Each d In ChkRange
  If InStr(1, " " & Replace(c.Value, ",", " ") & " ", " " & d.Value & " ", vbTextCompare) > 0 Then
LastRow2 = Dst.Cells(Rows.Count, "A").End(xlUp).Row + 1
c.EntireRow.Copy Dst.Cells(LastRow2, "A")

End If
Next
Next
End Sub

Thanks.

 

[prasaddn]

Hi,

You almost got it!

Try this code after you do copy paste:
Dst.Cells(LastRow2, "A").EntireColumn.AutoFit

Regards,
Prasad DN

 

[Deepak]

I asked for sample file not code however try this.

Dst.UsedRange.Columns.AutoFit
End Sub

 

[sealion1]

Hi Deepak / Prasad DN,

Thanks for both your help.

On this occasion Deepak's method worked - but thanks to you both!