vba code somehow makes the window tiny and almost cant see it

J

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
440
Office Version
  1. 2016
Hey all,

My vba code is somehow making one of the open workbooks like minimally small as small as you can make it. Like when you click in the upper right to make it half the screen size it makes it like 2 inches by 1/4 inch. Any ideas? Can I just have it make the window full size again?

THanks

Jordan
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
can you share your vba code with us to take a look at what it is doing?
 
Upvote 0
Candyman8019 said:
can you share your vba code with us to take a look at what it is doing?
Click to expand...
hey bud actually this is causing problems all across the recon. I assume its something outside the code since it never had this problem before but now its causing it to error out in other parts of the code. Here is the code where it initially does minimize the window.

VBA Code: 
Sub Variancesinserting()
'
' Variancesinserting Macro
'

'
 With Worksheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS")
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With

    Sheets("CARS TO HQARS BUMP").Select
    Range("C44:C70").Select
    Selection.Copy
     Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS").Select
'    With Worksheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS")
'    If .AutoFilterMode Then
'        If .FilterMode Then
'            .ShowAllData
'        End If
'    Else
'        If .FilterMode Then
'            .ShowAllData
'        End If
'    End If
'End With
    
    Dim lRow As Long

    
    'Find the last non-blank cell in column A(1)
    lRow = (Cells(Rows.Count, 10).End(xlUp).Row + 1)
    
   Range("J" & lRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K" & lRow).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+ABS(RC[-1])"
    Range("K" & lRow).Select
    

    
    Range("A" & lRow).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+LEFT(RC[40],2)"
    Range("A" & lRow).Select
    Dim lRow2 As Long


'Find the last non-blank cell in column A(1)
lRow2 = Cells(Rows.Count, 10).End(xlUp).Row

    Selection.AutoFill Destination:=Range("A" & lRow & ":A" & lRow2), Type:=xlFillDefault
'
'   Dim lRow2 As Long
'
'
''Find the last non-blank cell in column A(1)
'lRow2 = Cells(Rows.Count, 10).End(xlUp).Row
'
''MsgBox "lRow: " & lRow & vbCrLf & _
''     "lRow2: " & lRow2 & vbCrLf & _
'     "Selection: " & Selection.Address
     Range("K" & lRow).Select
If lRow2 > lRow Then
Selection.AutoFill Destination:=Range("K" & lRow & ":K" & lRow2), Type:=xlFillDefault

   
    End If
   
    Sheets("CARS TO HQARS BUMP").Select
    
    Range("D44:D70").Select
    Selection.Copy
     Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS").Select
    Range("AO" & lRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("A" & lRow).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+LEFT(RC[40],2)"
    Range("A" & lRow).Select
    Selection.AutoFill Destination:=Range("A" & lRow & ":A" & lRow2), Type:=xlFillDefault
   
    
    Range("B" & lRow).Select
    
    ActiveCell.FormulaR1C1 = "TREAS VAR"
    Range("B" & lRow).Select
    Selection.AutoFill Destination:=Range("B" & lRow & ":B" & lRow2), Type:=xlFillDefault
    
    Range("C" & lRow).Select
    
    ActiveCell.FormulaR1C1 = ThisWorkbook.Worksheets("Variables").Range("A6").Value
    
    Range("C" & lRow).Copy
        
        
    Selection.AutoFill Destination:=Range("C" & lRow & ":C" & lRow2), Type:=xlFillDefault
    Range("C" & lRow & ":C" & lRow2).Select
    Range("C" & lRow & ":C" & lRow2).Select
    Range("D" & lRow).Select
        
    ActiveCell.FormulaR1C1 = "FFFF"
      Range("D" & lRow).Copy
    
    Selection.AutoFill Destination:=Range("D" & lRow & ":D" & lRow2)
    Range("D" & lRow & ":D" & lRow2).Select
    Range("E" & lRow).Select
    
    ActiveCell.FormulaR1C1 = "=+MID(RC[36],4,4)"
    Range("E" & lRow).Select
    Selection.AutoFill Destination:=Range("E" & lRow & ":E" & lRow2), Type:=xlFillDefault
    Range("E" & lRow & ":E" & lRow2).Select
    Application.WindowState = xlNormal
    
    Range("AF" & lRow).Select
   
    Windows("Suspense automation.xlsm").Activate
   
       
    Range("AF15").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("AF" & lRow).Select
    
    Sheets("CARS TO HQARS BUMP").Select
    
    Range("D51").Select
    Selection.Copy
    Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS").Select
    
    Range("E2592").Select
   
    Rows("1:1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    Application.WindowState = xlMaximized
    ActiveWindow.FreezePanes = True
    Range("L18").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("AF" & lRow).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+MID(RC[9],13,1)"
    Range("AF" & lRow).Select
    Selection.AutoFill Destination:=Range("AF" & lRow & ":AF" & lRow2), Type:=xlFillDefault
    Range("AF" & lRow & ":AF" & lRow2).Select
    
    Range("AF" & lRow).Select
   
    ActiveCell.FormulaR1C1 = "=+IF(RIGHT(RC[9],3)=""TDD"","" "",MID(RC[9],13,1))"
    Range("AF" & lRow).Select
    Selection.AutoFill Destination:=Range("AF" & lRow & ":AF" & lRow2), Type:=xlFillDefault
    
 
    Sheets("CARS TO HQARS BUMP").Select
    Range("C44:C79").Select
    Selection.Copy
     Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS").Select
    Range("J" & lRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("CARS TO HQARS BUMP").Select
    
    Range("D44:D79").Select
    Application.CutCopyMode = False
    Selection.Copy
     Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS").Select
   
    Range("AO" & lRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
   
    Range("AH" & lRow).Select
    
    Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A14").Value & " IDARRS").Select
    
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+RIGHT(RC[7],9)"
    Range("AH" & lRow).Select
    ActiveCell.FormulaR1C1 = "=+RIGHT(RC[7],12)"
    Range("AH" & lRow).Select
    Selection.AutoFill Destination:=Range("AH" & lRow & ":AH" & lRow2), Type:=xlFillDefault
    Range("AH" & lRow & ":AH" & lRow2).Select
   
    Range("AH" & lRow).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RIGHT(RC[7],3)=""TDD"",RIGHT(RC[7],8),+RIGHT(RC[7],12))"
    Range("AH" & lRow).Select
    Selection.AutoFill Destination:=Range("AH" & lRow & ":AH" & lRow2)
    Range("AH" & lRow & ":AH" & lRow2).Select
    
    Range("AI" & lRow).Select
    ActiveCell.FormulaR1C1 = "NO"
    Range("AI" & lRow).Select
    Selection.AutoFill Destination:=Range("AI" & lRow & ":AI" & lRow2)
    Range("AI" & lRow & ":AI" & lRow2).Select
   
    Range("AJ" & lRow).Select
    ActiveCell.FormulaR1C1 = "COMPLETE"
    Range("AJ" & lRow).Select
    Selection.AutoFill Destination:=Range("AJ" & lRow & ":AJ" & lRow2)
    Range("AJ" & lRow & ":AJ" & lRow2).Select
   
    Range("AK" & lRow).Select
    ActiveCell.FormulaR1C1 = "=+RC[-27]"
    Range("AK" & lRow).Copy
    Selection.AutoFill Destination:=Range("AK" & lRow & ":Ak" & lRow2)
    Range("Ak" & lRow & ":Ak" & lRow2).Select
    Range("AL" & lRow).Select
    ActiveCell.FormulaR1C1 = "Supplemental JVs"
    Range("AI" & lRow).Select
    
    Range("AL" & lRow).Select
    Selection.AutoFill Destination:=Range("AL" & lRow & ":AL" & lRow2)
    Range("AL" & lRow & ":AL" & lRow2).Select
   
   Range("AR" & lRow).Select
    ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-43],""-"",RC[-1],""-"",RC[-10])"
   
    Range("AR" & lRow).Copy
    Selection.AutoFill Destination:=Range("AR" & lRow & ":AR" & lRow2)
    Range("AR" & lRow & ":Ak" & lRow2).Select
  
    Range("AQ" & lRow).Select
    
    Range("AP" & lRow).Select
   
    Range("AQ" & lRow).Select
    
    ActiveWindow.ScrollColumn = 36
    Range("AO" & lRow).Select
    
    Range("AQ" & lRow).Select
    ActiveCell.FormulaR1C1 = "=+MID(RC[-2],4,8)"
    Range("AQ" & lRow).Select
    Selection.AutoFill Destination:=Range("AQ" & lRow & ":AQ" & lRow2)
    Range("AQ" & lRow & ":AQ" & lRow2).Select
   
    
End Sub
 
Upvote 0
Nothing specific is jumping out at me. I would try stepping through the code one line at a time to see which one is causing your issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Shape objects distorting on Mac only (not Windows)
Replies
0
Views
122
rline101
R
N
  • Solved
VBA Excel opens Word - Split Screen to Windows Planes
Replies
5
Views
644
NdNoviceHlp
NdNoviceHlp
D
  • Question
Saving workbook "hides" macros
Replies
2
Views
371
dotsent
D
Joe4
  • Solved
Old VBA Code No Longer Working
Replies
3
Views
499
Micron
M
R
  • Question
Inserting a mailto link into an email body to a customer, based on a cell value.
Replies
3
Views
499
Domenic
D

Forum statistics

Threads
1,214,911
Messages
6,122,192
Members
449,072
Latest member
DW Draft

Share this page

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
Back
Top