Insert into visible cells only if there are visible cells

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
309
Office Version
  1. 2016
Hey Guys,

I have the following
Columns("J:J").Select
Range("aj2:aj6000").SpecialCells(xlCellTypeVisible).Formula = "COMPLETE"

Range("AM71").Select
Range("al2:al6000").SpecialCells(xlCellTypeVisible).Formula = "OFF SET"

Range("AL78").Select

it works great but only if there are cells that are selected when filtered
Columns("Au:Au").Select
Selection.Style = "Currency"
ActiveSheet.Range("$A$1:$AY$6000").AutoFilter Field:=47, Criteria1:= _
"=$(0.00)", Operator:=xlOr, Criteria2:="=$-"
sometimes there wont be any results though. It then errors out saying that there arent any cells to be inserted into because there wasnt anything in the selection. How can I code around this?

Jordan
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jordanburch()
   Dim UsdRws As Long
   
   With ActiveSheet
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A1:AY" & UsdRws).AutoFilter 47, "=$(0.00)", xlOr, "=$-"
      If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("J").Value = "COMPLETE"
         .AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("AI").Value = "OFF SET"
      End If
   End With
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
309
Office Version
  1. 2016

ADVERTISEMENT

You're welcome & thanks for the feedback.
Hey Fluff! Trying to resurect this thread. I am getting the error

Dim UsdRws As Long

With ActiveSheet
UsdRws = .Range("A" & Rows.Count).End(xlUp).Row

If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
.AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("AJ").Value = "COMPLETE"
.AutoFilter.Range.Offset(1).Resize(UsdRws - 1).Columns("AL").Value = "ODS ATG-D"
End If
End With


runtime error 6 overflow

If .Range("A1:A" & UsdRws).SpecialCells(xlVisible).Count > 1 Then
thats the error line.

Im not sure whats going on. It worked before. This time there is nothing to select and all visible cells are empty so it should skip over the code. Any help is appreciated!

jORDAN
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
Sounds like the data was already filtered when you ran the code.
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
309
Office Version
  1. 2016

ADVERTISEMENT

Sounds like the data was already filtered when you ran the code.
yes that is correct. This is the code directly above. Isnt that what the purpose is?

Range("av2:av6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'ODS ATG-D'!C[155],0)"
Range("a1:ax6000").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$av$6000").AutoFilter Field:=48, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues


basically if in field 48 there are matches then I want it to insert complete and ODS ATG D parts, if there arent any matches I want it to exit the sub. where should I insert the filer?

Jordan
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
You made no mention of running the code on previously filtered data.
Try removing the filter before you run the code.
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
309
Office Version
  1. 2016
You made no mention of running the code on previously filtered data.
Try removing the filter before you run the code.
it runs fine without filter. Basically i want it to insert those values after it is filtered if there are matches and if its blank just to end the sub.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
You need to apply the filters after you calculate UsdRws, otherwise you can get problems.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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