Skip rows

[q4s72534]

I'm writing a script in vb with excel files. I want to skip rows for cells with numbers 2 and 3 in them. how do i do this?

example1 to export:
a 1
b 2
c 3
j 0

How I want it to look?

a 1
j 0

 

[SkipVought]

Hi,

By skipping, do you mean that you don't want to DELETE the data, just hide it for reporting purposes?

If so...

with activesheet.usedrange
  for r = .row to .row + .rows.count - 1
    for c = .column to .column + .columns.count - 1
      with cells(r, c)
        select case .value
          case 2, 3
            .entirerow.hidden=true
            exit for
        end select
      end with
    next
  next
end with

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Hi q4s72534,

If you like, I can email you an example file that uses VBA and Excel's Advanced Filter. You can either "filter-in-place", or extract a copy of your required data to a separate sheet.

If you prefer, you could email me a file showing your file structure. This way, I could "tailor" the VBA to your actual situation.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[q4s72534]

this may help

Public Function Export() As Boolean
On Error GoTo errorStuff

Dim retValue As Boolean
retValue = False
Dim filename As String
Dim NumRows As Long, NumCols As Integer
Dim r As Long, c As Integer
Dim Data
Dim Date_Data
Dim ExpRng As Range
Dim CallingWorkbook As String
CallingWorkbook = ActiveWorkbook.name
If CallingWorkbook <> Card Then
Workbooks.Open (BaseDirectory & &quot;\&quot; & GMCCard)
End If
Windows(GMCCard).Activate
Sheets(&quot;gmc_Metrics&quot.Visible = True
Worksheets(&quot;gmc_Metrics&quot.Activate
Set ExpRng = Range(&quot;a2:c272&quot
NumCols = ExpRng.Columns.Count
NumRows = ExpRng.Rows.Count
filename = &quot;c:\Metrics.csv&quot;
Open filename For Output As #1
For r = 1 To NumRows
For c = 1 To NumCols
Data = ExpRng.Cells(r, c).Value
Date_Data = ExpRng.Cells(r, 2).Value
If IsNumeric(Data) Then Data = Val(Data)
If (IsEmpty(ExpRng.Cells(r, c))) Then Data = &quot;&quot;

If (c <> NumCols) And (Date_Data > 20030404) Then
Write #1, Data;
Else
Write #1, &quot;&quot; 'Data &quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot; I want to skip the row if this is the case right here.
End If
Next c
Next r
Close #1
Sheets(&quot;gmc_Metrics&quot.Visible = False
If CallingWorkbook <> Card Then
ActiveWorkbook.Close
End If
retValue = True
GoTo noerror
errorStuff:
retValue = False
noerror:
Export = retValue

 

[SkipVought]

you stated...

               If (c <> NumCols) And (Date_Data > 20030404) Then
                    Write #1, Data;
                Else
                  Write #1, &quot;&quot; 'Data  &quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot; I want to skip the row if this is the case right here.
                End If

well, duh...

               If (c <> NumCols) And (Date_Data > 20030404) Then
                    Write #1, Data;
                End If

or am I missing something?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com